Convert numbers to hrs: min
 

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

Convert numbers to hrs: min
 

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!

"da" wrote:

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

Convert numbers to hrs: min
 

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

"Nate @ CST Global" wrote:

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!

"da" wrote:

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