Forum Discussion
Formatting time
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
Hi Nabil,
This process did not work. The formatting is not recognizing and converting the time part. Any other suggestions?
Thanks!
Another variant of above formula
=LEFT(A1,8)+(INT(RIGHT(E1,4)/100)+MOD(RIGHT(E1,4),100)/60)/24
