Work Week Hours Calculation problem

I am creating a work schedule per week.

I have done a simple formula =(A#-A#)+(B#-B#).... to calculate hours worked per week. Since one value of A had the time in, and the other value of A had time out, I need to do that for each day. Is there an easier formula to calculate that?

Also, some days there are workers who have to work until closing. Closing is difficult to put a time, but to give a general value of 8:00 PM, how do I write that into the formula so that it remains as Close, but has the value of 8:00 PM.

I will upload the file with no names on it, so if it helps.

Thanks, laughingasian.

You should format the merged cell J5/J6 as [h]:mm, otherwise the hours will wrap at 24, then you can put this formula in that merged cell:

=(IF(C6="Close",--"20:00:00",C6)-C5)+(IF(D6="Close",--"20:00:00",D6)-D5)+(IF(E6="Close",--"20:00:00",E6)-E5)+(IF(F6="Close",--"20:00:00",F6)-F5)+(IF(G6="Close",--"20:00:00",G6)-G5)+(IF(H6="Close",--"20:00:00",H6)-H5)+(IF(I6="Close",--"20:00:00",I6)-I5)

and then this can be copied down. Note that you have a space before "Close" in E18, so you will need to get rid of the space to avoid the #Value error.

Hope this helps.

Pete

Hi there.

I have J5/J6 as [h]:mm already, thank you.

I cleared the space before Close, nice catch.

What is the -- between Close and 20:00:00? What function does that do?

laughingasian

That is the double-unary minus, which effectively multiplies by -1 and then by -1 again (i.e. equivalent to multiplying by 1). I put the time element in as a string value so that it is easier to read, but that string needs to be converted into a numeric value, which is what the -- does.

Hope this helps.

Pete