Converting time to decimal

[barc0de]

As posted in another thread, this is a great way to convert time to
decimal.

=(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))+0

example:

2342
=.9875

but if the time is 0, then it messes up (#VALUE). Is there a way t
solve this problem?

Thanks in advance

 

[lynxbci]

Hi again,

your formula doesn't seem to work BUT

if you wish to ignore a zero value then you could use :

=if(a1<>0,(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))+0,0)

so basically thats

=if(a1<>0,*your formula*,0)

this will put a 0 if the value is

 

[JE McGimpsey]

One alternative:

=TIME(INT(A1/100),MOD(A1,100),0)

 

[barc0de]

Try the formula without the colon.

=(LEFT(A1,LEN(A1)-2)&""&RIGHT(A1,2))+0


This below did not seem to help.=if(a1<>0,(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))+0,0)

so basically thats

=if(a1<>0,*your formula*,0)

this will put a 0 if the value is 0 <<<<