Convert numbers to hrs: min

  • Thread starter Thread starter da
  • Start date Start date
D

da

Hello
How can I convert the following numbers into hrs:min?
I have tried every possible format (including custom) and I do not get the
results I want (e.g. 15:45 18:40)
I want to get the difference in time between these numbers.
Thank you

1545 1840
2015 2300
2045 2105
1700 1815
1825 1840
1850 2000
1835 1900
 
Assuming your values are in columns A & B, I used this formula in C1 to get
the result you are looking for:

=TEXT((LEFT(B1,2)&":"&RIGHT(B1,2))-(LEFT(A1,2)&":"&RIGHT(A1,2)),"HH:MM")

Using your sample data, these are the results:

1545 1840 02:55
2015 2300 02:45
2045 2105 00:20
1700 1815 01:15
1825 1840 00:15
1850 2000 01:10
1835 1900 00:25

Hope that helps!
 
Nate

Your formula gives the correct result, but as text. Could use:

=VALUE(TEXT((LEFT(B1,2)&":"&RIGHT(B1,2))-(LEFT(A1,2)&":"&RIGHT(A1,2)),"HH:MM"))

then format the number as time

Regards
Peter
 
Back
Top