Calculating hours based on dates for weekend/weekday/public holiday

TTVH 0
Viewed 28

I've been attempting to use networkdays.intl to calculate hours for a weekend/weekday and public holiday.

The calculation is correct except when the time starts in 1 day and ends in another day for a shift (IE: 9pm until 2am)

Formula used for weekday

=NETWORKDAYS.INTL(B2,D2,"0000011",$T$2:$T$7)*G2

Formula used for weekend

=NETWORKDAYS.INTL(B2,D2,"1111100",$T$2:$T$7)*G2

Formula used for public holiday (basic)

=SUM(I2-Q2-R2)

Example sheet with highlighted cells causing the miscalculation.

Calculating hours on dates

Appreciate any suggestions.

date google-sheets-formula google-sheets
1 Answers

enter image description here

Q12:

=INDEX(TEXT(SUM(QUERY({TEXT(SEQUENCE(D12-B12+1, 1, B12), {"ddd", "#"}), 
 {1-C12; SEQUENCE((D12-B12+1)-2, 1, 1, ); E12}}, 
 "select Col3 
  where not Col1 matches 'Sat|Sun' 
    and not Col2 matches '"&TEXTJOIN("|", 1, IFERROR(1/(1/T2:T)))&"'")*1), "[h]:mm:ss"))

R12:

=INDEX(TEXT(SUM(IFNA(QUERY({TEXT(SEQUENCE(D12-B12+1, 1, B12), {"ddd", "#"}), 
 {1-C12; SEQUENCE((D12-B12+1)-2, 1, 1, ); E12}}, 
 "select Col3 
  where Col1 matches 'Sat|Sun' 
    and not Col2 matches '"&TEXTJOIN("|", 1, IFERROR(1/(1/T2:T)))&"'"))*1), "[h]:mm:ss"))

S12:

=INDEX(TEXT(SUM(IFNA(QUERY({TEXT(SEQUENCE(D12-B12+1, 1, B12), {"ddd", "#"}), 
 {1-C12; SEQUENCE((D12-B12+1)-2, 1, 1, ); E12}}, 
 "select Col3 
  where Col2 matches '"&TEXTJOIN("|", 1, IFERROR(1/(1/T2:T)))&"'"))*1), "[h]:mm:ss"))
player00
Related