Issue

This Content is from Stack Overflow. Question asked by TTVH

Huge thanks to @Osm who solved my previous dilemma of formulating desired hours between 2 dates.

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

Same issue as previous, 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.

Solution

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"))

This Question was asked in StackOverflow by TTVH and Answered by player0 It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.