Convert numbers to hrs: min

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
 
N

Nate @ CST Global

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!
 
B

Billy Liddel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert to hrs:min 2
Convert hrs to date but 1day=7.5hrs not 24hrs 1
convert decimal numbers to a fraction of an hour for payroll hour 4
How do I compare a list of Lotto winning numbers to my ticket? 4
How to convert numbers to text (e.g 10 = Ten) 3
how can i convert numbers to text? e.g 1 to "one", 11 to "eleven" 2
Timecard: convert time h:mm to number (example 8.75 hrs) to add totals 1
How to convert 1 min Stock Data into Desired Time Frame 1

Top