Timesheet problem:distinguishes between Normal Time and Overtime,

I need to setup a timesheet that distinguishes between Normal Time and Overtime, with Overtime being work between the hours of 22:30 and 06:30, and all other time is Normal.
What I need is how to do this for the yellow cells in the attached sample.

Unfortunately those links don't cover what my problem is - I've already tried all the links I could find but none of them cover the area of my problem.

You may be able to do this more easily depending on what shifts are possible but this should give you the result you want assuming all shift are less than 24 hours long.

In E5

=(C5>D5)*MEDIAN(0,D5-"6:30","16:00"+0)+MAX(0,MIN("22:30"+0, D5+(C5>D5))-MAX("6:30"+0,C5))

and in F5

=MOD(D5-C5,1)-E5

format both as time and copy down

If your overtime period might be variable you can alter the hardcoded times in the formula for cell references to more easily change the period, note "16:00" represnts the length of the non-overtime period

Thanks daddylonglegs that works, however is there any way I can get the result as a number rather than time. There is a series of calculations that are based on the result, and these won't work if the format is time.

If you want the result in decimal hours, e.g. 8.5 rather than 8:30 then just multiply by 24 so the above formulas would become:

=((C5>D5)*MEDIAN(0,D5-"6:30","16:00"+0)+MAX(0,MIN("22:30"+0, D5+(C5>D5))-MAX("6:30"+0,C5)))*24

and

=(MOD(D5-C5,1)-E5)*24

format as number

I am getting an error in F5, but correct result in E5

Apologies, I just multiplied the F5 formula by 24 but, of course, because it refers to E5 and that has now also been multiplied by 24 I need to adjust accordingly. Change F5 to

=MOD(D5-C5,1)*24-E5

Many thanks daddylonglegs, works beautifully.