• 464K Members
• 9,001 Online
• 561K Conversations

New Contributor

# Formatting time

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

8 Replies

# Re: 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

Hope that helps

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

# Re: Formatting time

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.

# Re: Formatting time

Hi Nabil,

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

Thanks!

# Re: Formatting time

Another variant of above formula

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

# Re: Formatting time

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

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

# Re: Formatting time

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.

# Re: Formatting time

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.

# Re: Formatting time

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies