Formulas for calculating timesheet

**Val C** · 05-24-2010, 09:01 PM

Hello,

Sorry to be such a pain, but I've extended my original timesheet to include annual leave, personal leave and public holidays. What my problem is that when someone is away for the day for either public, personal or annual leave in the the o/t time it calculates itself to read 6. I'm not sure how to fix this. Can someone have a look at the timesheet I've attached and advise me on how to fix this problem.

Many thanks,
Val C

A big THANK YOU to everyone's help.

**NBVC** · 05-24-2010, 09:28 PM

Try initiating the formula with a check to see if entries are made for the day:

=IF(SUM(C20:F20)=0,"",IF((((D20-C20)+(F20-E20))*24),((D20-C20)+(F20-E20))*24)-IF((((D20-IF(C20<G$9,G$9,C20))+(IF(F20>H$9,H$9,F20)-E20))*24),((D20-IF(C20<G$9,G$9,C20))+(IF(F20>H$9,H$9,F20)-E20))*24))

so if C20:F20 are blank, then return a blank, otherwise proceed with the calculations.

You can substitute a 0 for the "" to return 0 instead, if preferred.

Do similar for other columns (or you can point to the previous result and indicate if that result is blank, then blank, else continue).

Note: If you are leaving Saturday/Sunday fields blank or entering 0's, then you can use SUM() formulas at the bottom to avoid the #VALUE errors...

**Val C** · 05-24-2010, 11:20 PM

Hi, I'm not that converse with excel yet, I'm still very much a beginner. I used the calculations as stated and it did come up blank. Not sure if that was what I wanted. What I'm wanting to do is when someone is not at work on a particular day say they have a day off for personal leave and they record 7.6 in personal leave and leave c-f blank, it comes up with a 6 in the overtime column. I don't want it to reflect this is want it to say 0. I hope I'm making myself clear and you can understand my question.

Thanks,

**Val C** · 05-24-2010, 11:32 PM

Originally Posted by Val C

Hi, I'm not that converse with excel yet, I'm still very much a beginner. I used the calculations as stated and it did come up blank. Not sure if that was what I wanted. What I'm wanting to do is when someone is not at work on a particular day say they have a day off for personal leave and they record 7.6 in personal leave and leave c-f blank, it comes up with a 6 in the overtime column. I don't want it to reflect this is want it to say 0. I hope I'm making myself clear and you can understand my question.

Thanks,

Sorry I've jumped the gun that works fine, except now the extra time column comes up with a #value. I guess I've put in the wrong calculations in this column and back to the drawing board I go. Any suggestions or advice would be greatly appreciated.

I have to say this is the best kind of program and support I've come across and it is great for people like myself who are beginners with the excel program.

Thanks,

**NBVC** · 05-25-2010, 07:31 AM

I'm not exactly sure what you mean... but if you mean the totals at the bottom are coming up with #VALUE errors, then re-read my original post... I think if you change the formula in G28 to : =SUM(G13:G26) and copy to next column.. you will get the results you need. This formula ignores blanks.

See attached.