Number of hours worked between 18:00 and 06:00

[clobns]

On a timesheet, an employee earns a special rate when their work hours
go beyond 18:00, but end at 06:00. I can't figure out a formula to
calculate the number of hours an employee works between these time
periods.

Examples: An employee works 00:00-08:00, they would earn a special pay
rate for the six hours worked from 00:00-06:00
or
An employee works 16:00-24:00, they would earn a special rate for the 6
hours worked from 18:00-24:00.
or
An employee works 12:00-20:00, they would earn a special rate for the 2
hours worked from 18:00-24:00

Help!!!! Anybody with an answer?????

 

[Jerry W. Lewis]

=IF(B1<A1,IF(A1<0.75,18-24*A1,0)+IF(B1*24>8,B1*24-8,0)&"+"&IF(A1<0.75,6,24*A1-18)+IF(B1*24>8,8,B1*24),

IF(A1<0.75,IF(A1*24>8,IF(B1<0.75,(24*B1-24*A1)&"+0",18-24*A1&"+"&24*B1-18),
IF(B1>0.75,18-8&"+"&8-24*A1+24*B1-18,B1*24-8&"+"&8-24*A1)),"0+"&24*B1-24*A1))

will return a text string
normalHrs &"+"& specialHrs

Jerry

 

[DOR]

If you want the normal start and end times to be variable, the
following is a generalized formula that gives you the normal time,
where NS is the normal period start time (6:00, in your case) and NE is
the normal period end time, assuming that, within the day, end follows
start:

With work start time in A2 and work end time in B2,

Normal work time is:

=IF(B2>A2,MAX(0,MIN(NE,B2)-MAX(NS,A2)),MIN(NE-NS,MAX(0,NE-A2)+MAX(0,B2-NS)))

Total work time is:

=(B2-A2+IF(B2<A2,1))

Total Work Time minus Normal work Time provides special time.

Assumes that all times are entered in the format h:mm and are therefore
less than 1 in absolute value. Also assumes that no working shift
exceeds 23:59, although it does work for a 0:00 start time and a 24:00
end time.

Convert results to hours by multiplying result by 24.

HTH