| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number to time
Help
I have 2 columns both with 4 digits as a time 1341 1415 . thease fields are brought in from another system and can not be changed to a time format what i am trying to do is work out the difference if it takes away 1341 from 1415 as a time rather than a number, so i get 34 as the result instead of 74 as i get at present. Thank you in advance for help Stan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
number to time
If you have always got four digits, then you can convert each into a
time with the following: =VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00"), and =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00") Format them as Time. So, if you just want the difference in minutes then: =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00")- VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00") and format as [mm]:ss (assumes B1 is a later time than A1). Hope this helps. Pete On May 3, 4:25 pm, Stan Halls wrote: Help I have 2 columns both with 4 digits as a time 1341 1415 . thease fields are brought in from another system and can not be changed to a time format what i am trying to do is work out the difference if it takes away 1341 from 1415 as a time rather than a number, so i get 34 as the result instead of 74 as i get at present. Thank you in advance for help Stan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
number to time
Appoliges for this ,,,, that worked great but..... i did not realise that if
the time was before 1000 it droped to a 3 digit number ie 0936 shows as 936 , any ideas how to add this to the below ? "Pete_UK" wrote: If you have always got four digits, then you can convert each into a time with the following: =VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00"), and =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00") Format them as Time. So, if you just want the difference in minutes then: =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00")- VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00") and format as [mm]:ss (assumes B1 is a later time than A1). Hope this helps. Pete On May 3, 4:25 pm, Stan Halls wrote: Help I have 2 columns both with 4 digits as a time 1341 1415 . thease fields are brought in from another system and can not be changed to a time format what i am trying to do is work out the difference if it takes away 1341 from 1415 as a time rather than a number, so i get 34 as the result instead of 74 as i get at present. Thank you in advance for help Stan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
number to time
sorted, i reset to page format to make it show 4 didgets
Thank you for your help "Stan Halls" wrote: Appoliges for this ,,,, that worked great but..... i did not realise that if the time was before 1000 it droped to a 3 digit number ie 0936 shows as 936 , any ideas how to add this to the below ? "Pete_UK" wrote: If you have always got four digits, then you can convert each into a time with the following: =VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00"), and =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00") Format them as Time. So, if you just want the difference in minutes then: =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00")- VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00") and format as [mm]:ss (assumes B1 is a later time than A1). Hope this helps. Pete On May 3, 4:25 pm, Stan Halls wrote: Help I have 2 columns both with 4 digits as a time 1341 1415 . thease fields are brought in from another system and can not be changed to a time format what i am trying to do is work out the difference if it takes away 1341 from 1415 as a time rather than a number, so i get 34 as the result instead of 74 as i get at present. Thank you in advance for help Stan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
number to time
nope spoke to soon
"Stan Halls" wrote: sorted, i reset to page format to make it show 4 didgets Thank you for your help "Stan Halls" wrote: Appoliges for this ,,,, that worked great but..... i did not realise that if the time was before 1000 it droped to a 3 digit number ie 0936 shows as 936 , any ideas how to add this to the below ? "Pete_UK" wrote: If you have always got four digits, then you can convert each into a time with the following: =VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00"), and =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00") Format them as Time. So, if you just want the difference in minutes then: =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00")- VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00") and format as [mm]:ss (assumes B1 is a later time than A1). Hope this helps. Pete On May 3, 4:25 pm, Stan Halls wrote: Help I have 2 columns both with 4 digits as a time 1341 1415 . thease fields are brought in from another system and can not be changed to a time format what i am trying to do is work out the difference if it takes away 1341 from 1415 as a time rather than a number, so i get 34 as the result instead of 74 as i get at present. Thank you in advance for help Stan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
number to time
Okay, you can change it to the following to cope with 3 or 4 digit
times in either or both A1 and B1: =IF(LEN(B1)=3,VALUE(LEFT(B1,1)&":"&RIGHT(B1,2)&": 00"),VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00"))- IF(LEN(A1)=3,VALUE(LEFT(A1,1)&":"&RIGHT(B1,2)&": 00"),VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00")) I've assumed that only leading zeroes in the hour will be suppressed, so that 903 represents 9:03, but 1003 would be used for 10:03. Hope this helps. Pete On May 3, 9:36 pm, Stan Halls wrote: nope spoke to soon "Stan Halls" wrote: sorted, i reset to page format to make it show 4 didgets Thank you for your help "Stan Halls" wrote: Appoliges for this ,,,, that worked great but..... i did not realise that if the time was before 1000 it droped to a 3 digit number ie 0936 shows as 936 , any ideas how to add this to the below ? "Pete_UK" wrote: If you have always got four digits, then you can convert each into a time with the following: =VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00"), and =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00") Format them as Time. So, if you just want the difference in minutes then: =VALUE(LEFT(B1,2)&":"&RIGHT(B1,2)&":00")- VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00") and format as [mm]:ss (assumes B1 is a later time than A1). Hope this helps. Pete On May 3, 4:25 pm, Stan Halls wrote: Help I have 2 columns both with 4 digits as a time 1341 1415 . thease fields are brought in from another system and can not be changed to a time format what i am trying to do is work out the difference if it takes away 1341 from 1415 as a time rather than a number, so i get 34 as the result instead of 74 as i get at present. Thank you in advance for help Stan- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
number to time
=1440*(TIME(LEFT(B1,2),RIGHT(B1,2),0)-TIME(LEFT(A1,2),RIGHT(A1,2),0))
-- David Biddulph "Stan Halls" wrote in message ... Help I have 2 columns both with 4 digits as a time 1341 1415 . thease fields are brought in from another system and can not be changed to a time format what i am trying to do is work out the difference if it takes away 1341 from 1415 as a time rather than a number, so i get 34 as the result instead of 74 as i get at present. Thank you in advance for help Stan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| time into a number | Excel Discussion (Misc queries) | |||
| Every time i put a number with period it becomes a date and time | Excel Discussion (Misc queries) | |||
| Formula Help - Number to Time | Setting up and Configuration of Excel | |||
| Time as number | Excel Discussion (Misc queries) | |||
| Time to number | Excel Worksheet Functions | |||
Hybrid Mode
