I am creating a timesheet for overnight employees. I have developed the following formula:

=IF((IF(F13<C13, F13+1-C13, F13-C13)*24)>8,8,IF(F13<C13, F13+1-C13, F13-C13)*24)where F13 is end time and C13 is start time. (Leaving room for expansion, we currently pay for lunches)

The following formula was my original (with the lunch break included) but would not calculate the overnight hours properly:

=IF((((D13-C13)+(F13-E13))*24)>8,8,((D13-C13)+(F13-E13))*24)

Can anyone simplify this formula for me? There must be an easier way! I'd love to use the same formula across the entire workbook.

not sure it's much simpler but... =MIN((IF(D2<C2,1+D2-C2,D2-C2)+IF(F2<E2,1+F2-E2,F2-E2))*24,8)Will work out even with lunch breaks and times across the night. This assumes no one is working more than 24 hour days, and there's a max of 8 hours a day (think that's what your original formula was doing)

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History