Home

Formatting time

%3CLINGO-SUB%20id%3D%22lingo-sub-743388%22%20slang%3D%22en-US%22%3EFormatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743388%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20doing%20a%20time%20study%20and%20have%20some%20date%20time%20data%20that%20was%20given%20to%20me%20in%20a%20format%20where%20the%20time%20does%20not%20have%20a%20colon%20in%20it%20so%20formatting%20the%20cells%20is%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20115px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122613iB4C0224AD338D7BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-08_11-30-35.jpg%22%20title%3D%222019-07-08_11-30-35.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20easily%20convert%20this%20to%20a%20date%2Ftime%20format%3F%26nbsp%3B%20I%20started%20manually%20adding%20the%20colon%20to%20the%20times%2C%20but%20there%20are%20thousands%20of%20these%20cells.%26nbsp%3B%20Help%20please%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-743388%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743448%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372924%22%20target%3D%22_blank%22%3E%40thompsonk12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EAssuming%20that%20your%20Date%20and%20Time%20where%20entered%20correctly%2C%20they%20should%20be%20right%20aligned.%3C%2FP%3E%3CP%3EAnd%20because%20a%20Date%20is%20an%20incremental%20number%20(Day%201%20is%20the%201st.%20of%20January%201900)%2C%20and%20Time%20is%20a%20Decimal%20fraction%20of%20a%20Day.%3C%2FP%3E%3CP%3EWith%20this%20assumption%20in%20mind%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20a%20certain%20number%20of%20Cells%20in%20Column%20B%20(just%20to%20the%20right%20of%20your%20Date%20values)%20same%20size%20as%20the%20range%20in%20Column%20A%3A%20If%20you%20have%20Dates%20in%20the%20range%20A2%3AA1000%20then%20Select%20B2%3AB1000%3C%2FP%3E%3CP%3EThen%3C%2FP%3E%3CP%3EHit%20CTRL%20%2B%20R%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20copy%20the%20same%20values%20to%20column%20B%3C%2FP%3E%3CP%3ENow%20let's%20format%3C%2FP%3E%3CP%3ESelect%20Column%20A%20and%20Hit%3A%20CTRL%20%2B%20SHIFT%20%2B%203%26nbsp%3B%20(date%20Formatting)%3C%2FP%3E%3CP%3ESelect%20Column%20B%20and%20Hit%26nbsp%3B%20CTRL%20%2B%20SHIFT%20%2B%202%20(Time%20Formatting)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20see%20in%20the%20screenshot%20your%20Date%20%26amp%3B%20Time%20look%20like%20text%20which%20requires%20a%20different%20process%20according%20to%20the%20situation%20(may%20be%20type%201%20in%20a%20random%20cell%20%26gt%3B%26gt%3B%20copy%20this%20cell%20%26gt%3B%26gt%3B%20select%20Column%20A%20%26gt%3B%26gt%3B%20Hit%20ALT%20E%20S%20M%20consecutively)%20then%20follow%20the%20technique%20above.%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20there%20is%20a%20function%20to%20convert%20a%20Date%20entered%20as%20Text%20into%20a%20number%3A%3C%2FP%3E%3CP%3ESo%20say%20in%20A1%20you%20have%20the%20Text%3A%2028%20Nov%202018%2C%20if%20you%20type%20in%20B1%26nbsp%3B%3C%2FP%3E%3CP%3E%3DDateValue(A1)%20it%20will%20return%2043432%20which%20is%20the%20corresponding%20date%20as%20an%20incremental%20number%20from%201%20Jan%201900%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20a%20link%20to%20a%20complete%20Guide%20on%20Date%20Math%20in%20Excel%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743523%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743523%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372924%22%20target%3D%22_blank%22%3E%40thompsonk12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3DDATEVALUE(LEFT(A1%2C8))%2BTIMEVALUE(MID(A1%2C10%2C2)%26amp%3B%22%3A%22%26amp%3BRIGHT(A1%2C2))%3C%2FPRE%3E%0A%3CP%3Efor%20the%20first%20cell%20in%20next%20column%2C%20select%20this%20column%20starting%20from%20this%20cell%20till%20end%20of%20the%20range%2C%20Ctrl%2BD.%20On%20the%20range%20Ctrl%2B1%20and%20apply%20custom%20mm%2Fdd%2Fyy%20hh%3Amm%20format%20to%20the%20range.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20assuming%20mm%2Fdd%2Fyy%20is%20your%20default%20format%20for%20the%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743554%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743554%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Nabil%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20process%20did%20not%20work.%26nbsp%3B%20The%20formatting%20is%20not%20recognizing%20and%20converting%20the%20time%20part.%26nbsp%3B%20Any%20other%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743569%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372924%22%20target%3D%22_blank%22%3E%40thompsonk12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20of%20above%20formula%3C%2FP%3E%0A%3CPRE%3E%3DLEFT(A1%2C8)%2B(INT(RIGHT(E1%2C4)%2F100)%2BMOD(RIGHT(E1%2C4)%2C100)%2F60)%2F24%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744241%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744241%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372924%22%20target%3D%22_blank%22%3E%40thompsonk12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%2C%20formatted%20as%20%22mm%2Fdd%2Fyyyy%20hh%3Amm%22%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DDATEVALUE(LEFT(A1%2C8))%2B%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ETIME(MID(A1%2C10%2C2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERIGHT(A1%2C2)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746122%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372924%22%20target%3D%22_blank%22%3E%40thompsonk12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20your%20posted%20example%20data%20is%20representative%20of%20all%20your%20data%2C%20then%20I%20think%20this%20formula%20should%20work...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D0%2BREPLACE(A1%2C12%2C0%2C%22%3A%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20cell%20with%20the%20Custom%20Format%20of%20your%20choosing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746397%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372924%22%20target%3D%22_blank%22%3E%40thompsonk12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20you%20load%20the%20data%20in%3C%2FSPAN%3Eto%20Power%20Query%20and%20type%20a%20few%20example%20outputs%2C%20PQ%20will%20generate%20and%20apply%20the%20edits%20%3CSPAN%3Eto%20the%20entire%20data%20field%20%3C%2FSPAN%3Efor%20you.%26nbsp%3B%20I%20had%20to%20set%20a%20US%20locale%20in%20order%20to%20convert%20from%20US%20to%20rest%20of%20world%20date%20formatting.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746432%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746432%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F161043%22%20target%3D%22_blank%22%3E%40Frederick%20Rothstein%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372924%22%20target%3D%22_blank%22%3E%40thompsonk12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20your%20posted%20example%20data%20is%20representative%20of%20all%20your%20data%2C%20then%20I%20think%20this%20formula%20should%20work...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D0%2BREPLACE(A1%2C12%2C0%2C%22%3A%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20cell%20with%20the%20Custom%20Format%20of%20your%20choosing.%3C%2FP%3E%0A%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3EIf%20you%20wanted%20to%20fix%20your%20data%20in%20place%2C%20you%20could%20use%20this%20macro...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3ESub%20FixDateTime()%3CBR%20%2F%3E%26nbsp%3B%20With%20Range(%22A1%22%2C%20Cells(Rows.Count%2C%20%22A%22).End(xlUp))%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20.Value%20%3D%20Evaluate(%22IF(%7B1%7D%2CREPLACE(%22%20%26amp%3B%20.Address%20%26amp%3B%20%22%2C12%2C0%2C%22%22%3A%22%22))%22)%3CBR%20%2F%3E%26nbsp%3B%20End%20With%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3ENote%3A%20If%20you%20have%20a%20header%20in%20cell%20A1%2C%20then%20change%20the%20%22A1%22%20to%20%22A2%22%20in%20the%20With%20statement.%3C%2FP%3E%3C%2FLINGO-BODY%3E
thompsonk12
New Contributor

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.

 2019-07-08_11-30-35.jpg

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 :)

8 Replies

@thompsonk12 

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

This tutorial is a Festival of Date Formulas & Functions! With over 40 examples that will allow you to hold a firm grasp of Date Math. I also included on a separate sheet a full definition of each function with the arguments required to use them. You can Download the Exercise file and Follow along

@thompsonk12 

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.

Hi Nabil,

 

This process did not work.  The formatting is not recognizing and converting the time part.  Any other suggestions?

 

Thanks!

@thompsonk12 

Another variant of above formula

=LEFT(A1,8)+(INT(RIGHT(E1,4)/100)+MOD(RIGHT(E1,4),100)/60)/24

@thompsonk12 

Try this formula, formatted as "mm/dd/yyyy hh:mm": 

=DATEVALUE(LEFT(A1,8))+
TIME(MID(A1,10,2),
RIGHT(A1,2),0)

@thompsonk12 

 

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.

@thompsonk12 

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. 

 


@Frederick Rothstein wrote:

@thompsonk12 

 

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies