Timesheet Total hours error

**fun123** · 05-25-2009, 03:01 AM

I am working on timesheet. I am using formula =((F17-E17+(F17<E17))-(I17-H17+(I17<H17)))*24. to calculate total hours

I have 4 coloums i.e E = TIme in , F= Time out and H= Break in , I= Break out. I want formula show total hours as "0" or blank if employee forget to put TIME IN or TIME OUT or BREAK IN or BREAK OUT. Currently total hours shows sometime 23:00 hrs or sometime negative value if employee forget to put time in any of the above mentioned columns.

Also I am using macro button as input time i.e

Button1 = Clock IN
Button2 = Clock out
Button3 = Break IN
Button4 = Break OUt

Is there any way to disable these macro buttons until next day once employee clicked any button.

Appreciate if somebody help me to solve these problems.

**DonkeyOte** · 05-25-2009, 04:02 AM

In terms of formula:

=IF(COUNT(E17:F17,H17:I17)=4,24*(MOD(F17-E17,1)-MOD(I17-H17,1)),0)

**fun123** · 05-25-2009, 04:09 AM

Ty for prompt reply.

Your formula solved my problem i.e show "0" if data missing in 4 columns i.e E,F,H,I but Now I am getting ##### if all the columns filled with data.

Thanks again for your help n time.

**DonkeyOte** · 05-25-2009, 04:18 AM

That would imply that

a) the result cell is set to Time Format
b) the result is negative

that would not make sense on either count ... ie given you're multiplying result by 24 you're showing result in integer terms (ie 10:30 = 10.5) and as such the result cell should be formatted to General / Number rather than Time.

Can you post a sample ?