HOURS whats the difference of DAY hours & NIGHT hours per SHIFT formula

Hello All! File & Photo are attached below.

Spreadsheet Purpose: WORK HOURS per MTH with breaks & night time rate exta including Year Summary of Hours, Pay & Miles.

Question/Issue: Need Formula to calculate number of night hours worked in a shift, using 4 values (Detailed below).
Currently is manual edit of total hours in Month Tabs (Jan-Dec) Columns AB, did try various formulas such as Sumproduct but this didn't work with cell formatting of hours [hh]:mm:

1. Start Time = Column F. Cell Format: [hh]:mm
2. Finish Time = Column G. Cell Format: [hh]:mm
3. Night Shift Hours Definition from = Cell AK17 (00:00)
4. Night Shift Hours Definition to = Cell AK20 (06:00)

Formulas of Night Shift see tab JAN:
COLUMN AB: Night Hours total per shift (Currently manual edit for example 6 hours, so FORMULA needed here!)
COLUMN AC: £ Night Hours amount extra total per shift manual edit
COLUMN AD: £ Night Hours amount extra total per shift manual edit

After lots of Googling and various edits, the manual edit is the only working version, even after many sparkling drinks and stargazing, but in Excel 2003, the many formulas I tried didn' work with the Time/Date cell formatting, or were more aligned with metric calculation.

Cheers

HoursXL.png

Solved it using formula from old post moderator (Daddy Long Legs) suggestion from upload file.

NIGHT HOURS CALC:
=IF(F2>0,(F2>G2)*MEDIAN(0,G2-$AD$17,$AD$20-$AD$17)+MAX(0,MIN($AD$20,G2+(F2>G2))-MAX($AD$17,F2)),"")

F2 = Start Shift
G2 = End Shift
AD17 = Nite Hrs Threshold Start
AD20 = Nite Hrs Threshold Finish

DAY HOURS CALC:
=IF(G2>0,MOD(G2-F2,1)-T2,"")

F2 = Start Shift
G2 = End Shift
T2 = Night Hrs Calc

Prev Post: timesheet-calculate-time-within-a-period-when-crossing-midnight

Completed solved file attached at btm of msg, XLS with auto calcs of: DAY & NIGHT HOURS (COLUMNS T & U):

using START & FINISH shift times (COLUMNS F & G)

with NIGHT TIME threshold start & end (CELLS AD17 & AD20)


Wow looks so simple now, to use:
copy & paste TIMES in START & FINISH (COLUMNS F & G) edit to suit
copy & paste PAY RATE in £HOUR (COLUMN J) edit to suit
copy & paste BREAKS in BREAK 1/BREAK 2/BREAK 3 (COLUMNS K , L & M) edit to suit
MILES per shift is calculated from BUSINESS CODE (COLUMN C) this is generated from tab CO (COLUMNS C & J)
enter figures manual per wk for NI/TAX/PENSION (COLUMNS Y, Z & AA)
or for per mth for TAX/NI/PENSION (CELLS F37, G37 & H37)

Summary TABS:
TOTAL = SHIFT day per day/wk/mth/year av/total
MILES = SHIFT mileage per wk/mth/year av/total

Attachment 754462