I've been trying to create a nested IF statement that will total the amount of hours based on a date range and the start / finish time (similar to a time sheet). This calculates hours "worked" based on, if the hours are continuously worked or part of each day. I've managed to get this returning the correct result based on a range of variables, however it is returning an incorrect result for one variable where it adds an excess amount of hours as it counts the total range of hours against the next day. This is where the start time is greater than the finish time and goes into the following date(s) (IE: A night shift).

Appreciate any suggestions as I've tried removing the 1 from the D12-B12 1 which results in returning an incorrect value for other entries.

I hope this makes sense.

Code:

=IF(F12="Yes",(P12-O12) * 24 *K12, IF(F12="No",(MOD(E12-C12,1)) * 24 * (IF(AND(D12=B12 1,E12<C12), 1,D12-B12 1)) * K12,0))

It need's to be set like this

Go to Format > Custom Date and time and set it Hour : Minute : Second.

And set the Hours in your case Q2 to Elapsed Hour : Minute : Second

The Formula

Paste this on the column of "Hours" in your case Q2

=IF(IF(C2=E2=TRUE,C2=E2,C2>E2)=TRUE,IFS(AND(C2=0,C2=E2),1,E2<C2,((1-C2) E2),1=1,E2-C2)*DATEDIF(B2,D2,"D"),(IFS(AND(C2=0,C2=E2),1,E2<C2,((1-C2) E2),1=1,E2-C2)*DATEDIF(B2,D2,"D")) IFS(AND(C2=0,C2=E2),1,E2<C2,((1-C2) E2),1=1,E2-C2))

Steps

This is seemingly intimidating when you look at the nested fuctions but once you see this