Excel Hours formula

Question

Hello,

I am using the following formula.

=SUM((C1/24)*12.07%)-A1+B2

It takes the number of decimal hours (i.e 20.75) in cell A1, coverts it to hours and then calculates the holiday pay percentage on that. Takes off hours paid and previous hours due. It works just fine. Until someone is paid more than they are due. Then the formula breaks and displays infinite hash #.

How would I have to adjust the formula to make it work when the holiday pay hours due is a negative?

Thank you,

Andy

Answer 1

Thank you so much for looking into this and your explanation is really helpful. Your workaround wouldn't really work for our requirements. But the time could be shown as a decimal rather than 24hrs, so I shall look at rebuilding the formula that way. Thanks again. Andy

Answer 2

Would it be possible to have the formula show a negative value?

Andy,

The negative value isn't the problem, your formula calculates that. However, if you format the cell with a time format, the cell will show #### because there are no negative times.

Simple workaround:

A1: 1:30

B1: =-A1

A:B formatted as time

C1: =B1*24

C formatted as number

Another way:

C1: =ABS(B1)

C formatted as time + a conditional formatting the makes the font red if B is negative.

Another way:

B1: =-A1
C1: =IF(B1<0,"-","")&TEXT(ABS(B1),"hh:mm")

But now you have a text in C and can no longer calculate with this value.

This are, more or less, the ways to go. It depends on your use case which is easiest.

Andreas.

Answer 3

Dear Andreas,

Thanks for you reply and for your help.

However, that formula does not give a true answer. It stops at 0.00. So if someone is paid too much, the overpayment isn't accounted for. Would it be possible to have the formula show a negative value?

There is no MIN or MAX, but the existing formula wouldn't allow for a negative answer.

Thanks,
Andy

Answer 4

=MAX(SUM((C1/24)*12.07%)-A1+B2,0)