TimeSheet Project

[joe brown]

Hi

Please help!!

i am working on a timesheet project and I want the below formula to meet two conditions ie (1) check if finish time in cell J7 is greater than start time in cell M7 if true then add one day to finish time cell so that it calculates the night shift hours correctly. It also has to take away break which start K7 and finishes L7.
(2) After multiplying the result by 24, it has to check if result set is greater than 10.5 hrs, as I want it to default to total shift hours of 10.5 and if this is more or less than 10.5 it has to be calculated as overtime/time owing displayed on another cell.
I will eventually want if to use a third condition “isblank”; so that when there is not start time/finish time; the total hours cell stays empty not displaying “0” because of the formula.

Many thanks


=(IF(AND(J7>M7,M7+1-J7,K7-J7)+(M7-L7))*24>10.5, ((K7-J7)+(M7-L7))*24)

[Alf]

Don't know if this could be of use to you but formula like this “=MOD(stop_time-start_time,1) will always give the right time difference even if it passes midnight.

Alf

[joe brown]

Hi Alf

That seem to have resolved my one side of the project. you're a star.

many thanks

Joe

[Alf]

You are welcome and thanks for feedback

so that it calculates the night shift hours correctly

What are the night shift hours, 22:00 to 06:00?

Alf