Jul 08 2019 11:41 AM
I am doing a time study and have some date time data that was given to me in a format where the time does not have a colon in it so formatting the cells is not working.
Is there a way to easily convert this to a date/time format? I started manually adding the colon to the times, but there are thousands of these cells. Help please :)
Jul 08 2019 12:30 PM - edited Jul 08 2019 12:42 PM
Hi,
Assuming that your Date and Time where entered correctly, they should be right aligned.
And because a Date is an incremental number (Day 1 is the 1st. of January 1900), and Time is a Decimal fraction of a Day.
With this assumption in mind:
Select a certain number of Cells in Column B (just to the right of your Date values) same size as the range in Column A: If you have Dates in the range A2:A1000 then Select B2:B1000
Then
Hit CTRL + R
To copy the same values to column B
Now let's format
Select Column A and Hit: CTRL + SHIFT + 3 (date Formatting)
Select Column B and Hit CTRL + SHIFT + 2 (Time Formatting)
However, I see in the screenshot your Date & Time look like text which requires a different process according to the situation (may be type 1 in a random cell >> copy this cell >> select Column A >> Hit ALT E S M consecutively) then follow the technique above.
By the way, there is a function to convert a Date entered as Text into a number:
So say in A1 you have the Text: 28 Nov 2018, if you type in B1
=DateValue(A1) it will return 43432 which is the corresponding date as an incremental number from 1 Jan 1900
here is a link to a complete Guide on Date Math in Excel
https://www.youtube.com/watch?v=qIZxeOq-QDk
Hope that helps
Nabil Mourad
Jul 08 2019 01:20 PM
You may use formula like
=DATEVALUE(LEFT(A1,8))+TIMEVALUE(MID(A1,10,2)&":"&RIGHT(A1,2))
for the first cell in next column, select this column starting from this cell till end of the range, Ctrl+D. On the range Ctrl+1 and apply custom mm/dd/yy hh:mm format to the range.
That is assuming mm/dd/yy is your default format for the date.
Jul 08 2019 01:39 PM
Hi Nabil,
This process did not work. The formatting is not recognizing and converting the time part. Any other suggestions?
Thanks!
Jul 08 2019 01:48 PM
Another variant of above formula
=LEFT(A1,8)+(INT(RIGHT(E1,4)/100)+MOD(RIGHT(E1,4),100)/60)/24
Jul 09 2019 01:05 AM
Try this formula, formatted as "mm/dd/yyyy hh:mm":
=DATEVALUE(LEFT(A1,8))+
TIME(MID(A1,10,2),
RIGHT(A1,2),0)
Jul 09 2019 01:55 PM - edited Jul 09 2019 01:59 PM
Assuming your posted example data is representative of all your data, then I think this formula should work...
=0+REPLACE(A1,12,0,":")
Format the cell with the Custom Format of your choosing.
Jul 09 2019 03:01 PM
If you load the data into Power Query and type a few example outputs, PQ will generate and apply the edits to the entire data field for you. I had to set a US locale in order to convert from US to rest of world date formatting.
Jul 09 2019 03:33 PM - edited Jul 09 2019 03:36 PM
Jul 09 2019 03:33 PM - edited Jul 09 2019 03:36 PM
@Frederick Rothstein wrote:
Assuming your posted example data is representative of all your data, then I think this formula should work...
=0+REPLACE(A1,12,0,":")
Format the cell with the Custom Format of your choosing.
If you wanted to fix your data in place, you could use this macro...
Sub FixDateTime()
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
.Value = Evaluate("IF({1},REPLACE(" & .Address & ",12,0,"":""))")
End With
End Sub
Note: If you have a header in cell A1, then change the "A1" to "A2" in the With statement.