# 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.```