Menu
Date column has date and time in different formats.
05-09-14 21:58
11-08-13 11:56
08/19/2016 11:08:46
11-08-13 11:56
11-08-13 12:16
05/24/2014 08:26:06
08/24/2016 11:00:29
12/20/2014 09:16:19
08/25/2016 09:38:22
08/24/2016 10:59:05
08/25/2016 12:36:33
08/19/2016 10:38:37
11-08-13 14:53
11-08-13 16:18
11-08-13 13:38
10-10-13 16:14
11-08-13 12:44
08/31/2016 17:13:57
I'm trying to convert these datetime into only date m/d/YYYY format. I tried =TEXT(cellofdate, "m/d/YYYY") but i'm still getting time for some entries.
955
Convert date/time format cell to date only with formulaSelect a blank cell you will place the date value, then enter formula =MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2) into the formula bar and press the Enter key.
Select the cells you want to format. Press CTRL+1. In the Format Cells box, click the Number tab. In the Category list, click Date, and then choose a date format you want in Type.
1. Select a blank cell next to your date, for instance. I1, and type this formula =TEXT(G1, "yyyy-mm-dd"), and press Enter key, then drag AutoFill handle over the cells needed this formula. Now all dates are converted to texts and shown as yyyy-mm-dd format.
First, pick the cells that contain dates, then right-click and select Format Cells. Select Custom in the Number Tab, then type 'dd-mmm-yyyy' in the Type text box, then click okay.
A date is just a number.
To the left of the decimal place is the date, to the right is the time.
=INT(A1) will return the whole number.
Your first example will display as 05/09/2014 00:00. All you need to do now is format the cell as a date without the time.
Edit: And read the post that @Ralph linked to - very informative.
167
If your concerned cell is A1, you can use the following expression :
=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
