I have tested the following to only a certain extent:
1) Helper columns (P:U) have wages, times, and days in rows 1:5
2) The formula for the two weekday segments before midnight is:
Formula:
=IF(WEEKDAY($B6,2)<=5,IF(AND($D6<P$4,$D6>=P$3),P$1,0)+IF($D6+1/24<P$4,(MIN($E6-$D6-1/24,P$4-$D6-1/24,$E6-P$3,P$4-P$3)*24)*P$2,0),"")
3) The formula for the after midnight weekday segment is:
Formula:
=IF(WEEKDAY($B6,2)<=5,IF($D6<R$4,R$1,0)+IF($D6+1/24<R$4,(MIN($E6-$D6-1/24,R$4-$D6-1/24,R$4)*24)*R$2,0),"")
4) The Saturday formulas are similar except that they begin with =IF(WEEKDAY($B6,2)=6...
5) The Sunday formula is: =IF(WEEKDAY($B6,2)=7,U$1+($E6-$D6-1/24)*24*U$2,"")
Let us know if you have any questions.
Bookmarks