|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 27th, 2010, 08:59 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
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 
|
|
#2
May 27th, 2010, 09:51 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
|
|
#3
May 27th, 2010, 11:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
