number to time

[Stan Halls]

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

 

[Pete_UK]

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

 

[David Biddulph]

=1440*(TIME(LEFT(B1,2),RIGHT(B1,2),0)-TIME(LEFT(A1,2),RIGHT(A1,2),0))

 

[Stan Halls]

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 ?

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

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

 

[Stan Halls]

sorted, i reset to page format to make it show 4 didgets

Thank you for your help

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 ?

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

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

 

[Stan Halls]

nope spoke to soon

sorted, i reset to page format to make it show 4 didgets

Thank you for your help

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 ?

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 <[email protected]>
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

 

[Pete_UK]

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

nope spoke to soon

sorted, i reset to page format to make it show 4 didgets

Thank you for your help

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 ?
:
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 <[email protected]>
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 -