Time Calculation

Posted by Brian on February 08, 2002 9:19 AM

Need some help in time calculation please!

I've custom formatted cells with 00\:00 to add the colon automatically. But now when I what to subtract and/or add times, it gives the wrong value, because the cells are not formatted for time. As an example, when I subtract 19:13 and 18:45 i get 68, when it should be 28 mins. Any way around this?

Posted by IML on February 08, 2002 9:42 AM

You could convert them into time by using
=LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2)+0
and then subtracting (remembering to format this cell as time)

Posted by Aladin Akyurek on February 08, 2002 10:25 AM

Ian --

Needs xxtra parens for good effect:

=(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))+0

Posted by Brian on February 08, 2002 10:49 AM

Thanks guys!!!