I have time data formatted together, like 1040 for 10 hours 40 minutes and 611 for 6 hours 11 minutes.



How can I change this data into form that Excel 2007 would understand it as time?

Hi Sammael,

We need to split apart the time into its respective hour and minute components by using string functions and then join them back together again as a valid time. Lets just say we have the times in column A



Follow these steps:

1. in a new column (I'll use column B) enter the following formula:
   =LEFT(A1,LEN(A1)-2)
   This will give you the hours.
2. in another column (I'll use column C) enter the next formula:
   =RIGHT(A1,2)
   This will give you the minutes.
3. in another column enter this formula to rejoin them back together again: =TIME(B1,C1,0)
4. This gives us the time however we need to convert it from a formula to a value because this formula is dependant on the other 2 previous formulas created in steps 1 and 2.
   Select all of the new times and copy and paste as values either in another column of over the top of itself.
5. Delete the formulas you create above leaving you with just the times

I hope this helps you out.

Or in one go perhaps and format as h:mm

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post