Calculating Hours Worked for the Week Including Absences

[Maya Bereaux]

I am helping someone with their compiled time sheet for their employees and would like to know how do you add the hours each worker worked for the week while also including absences in the time sheet?

I've already done up a formula to get the total hours worked for the week by adding additional columns next to each date to calculate the time worked for that day and then hid the columns(as can be seen by the yellow highlighted column in the yellow box. I expanded this column for a visual for this post; The formula I used for the hidden columns is underlined in yellow) then in the "total hours" column I added the results of the hidden columns to get the total hours worked for the week.

However my issue lies in when there are absences. I'm not sure how to get around this. I can't leave the cells blank since I need to put "absent" if a worker is absent for that day. But inputting absent messes up the formula and gives me an error. How do i get around this? Do i have to revise my formula? is there some IF formula i am supposed to be using? As of right now I do put the absent in the relevant columns and just put a text box with the  right number of hours worked over the error value in the total hrs column.

(PLEASE DON'T MIND HOW JANKEY THE BOTTOM LEFT OF THE PIC LOOKS I CUT THAT SECTION OUT BY MISTAKE IN 3D PAINT SO I HAD TO DO THEM OVER. I'm just getting familiar with the 3d paint program on windows 10)

[#2 · Accepted solution]

Greetings! Just add a small modification to the work you have already done, =iferror(b4-a4,0) Attaching file for your reference. Thanks,

[#3]

Hi Maya,

Please update the formula in cell P8 as follow:

=IF(OR(O8="ABSENT",N8="ABSENT"),"0",IF(O8<N8,O8+1,O8)-N8)

The idea of this update is to check whether cell O8 or cell N8 contains ABSENT.

If so, the formula will return 0, otherwise, it will go ahead to apply this calculation:

IF(O8<N8,O8+1,O8)-N8)

Regards

[#4]

Thanks! this was helpful!

[#5]

Thanks a lot! Taking note of the formula for future use. Will do some more reading up on the OR function as well

[#6]

I was having this same issue as well, but this seems like a quick fix. Thanks!
Also, I am experiencing a lot of inconsistencies when using the IF(B2<A2,B2+1,B2)-A2 formula when calculating time. I am using [h]:mm as my text format. It seems that the formula will randomly add 12 or 24 hours to the output. Does anyone know why excel would do this?