Fantastic formula needed

Posted by Robin on January 21, 2002 9:03 AM

Hi everyone,

Please help. I need a formula that will display the contents of a cell (A1) as hours and minutes. A1 itself contains a formula which calculates whether an employee is doing enough contract hours. Each unit in cell A1 represents 5 minutes so if A1 equaled 4 then that employee would be 20 minutes in credit. If A1 was equal to -20 then they would be 1 hour and 40 minutes in debt.

How can I enter a formula to split this into hours and minutes and also indicate the sign of A1..???

Thanks in advance,

Robin

Posted by Juan Pablo G. on January 21, 2002 9:11 AM

First, in Tools, Options, Check 1904 Date System.

Now, if A1 is 4 and A2 is -20, put this formula in B1:B2.

=A1*5/(24*60)

I didn't multiplied anything to let you see what i'm doing. First, multiply A1 * 5 minutes, and then divide this by 24 hours and then by 60 minutes per hour, in order to let Excel treat this as a date. Then format these cells as:

hh:mm

Juan Pablo G.

Posted by Robin on January 21, 2002 9:29 AM

1 question Juan Pablo

That's fantastic but will swapping to the 1904 date system mess anything else up?

Posted by Juan Pablo G. on January 21, 2002 9:32 AM

Re: 1 question Juan Pablo

Not if you're managing "modern" dates. Do you have any dates prior 1905 ?

Juan Pablo G.

Posted by Robin on January 21, 2002 9:37 AM

Re: 1 question Juan Pablo

Yeah, in other spreadsheets I have dates of birth from the 1800's.

Thanks anyway.