| Home |
| Search |
| Today's Posts |
|
#1
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
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
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I convert a column of positive numbers to negitive numbers | Excel Worksheet Functions | |||
| Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
| Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
| convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
| How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Linear Mode
