6 day/week function ie:networkday

[Guest]

i am trying to calculate how many days between 2 dates that excludes
holidays, but includes saturdays. Networkdays is only for 5 day work
weeks...i need it to be for 6 day work weeks.

 

[Guest]

This calculates number of Saturdays in a period so add this to NETWORKDAYS:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=7))

A1=Start date
B1=End date

 

[Peo Sjoblom]

This will count Saturdays with start date in A1 and end date in B1

=SUM(INT((WEEKDAY(A1-7)+B1-A1)/7))


this will count all days except Sundays

=SUM(INT((B1-WEEKDAY(A1+1-{2;3;4;5;6;7})-A1+8)/ 7))


this will count holidays with the holidays in L1:L12



=SUMPRODUCT(--(L1:L12>=A1),--(L1:L12<=B1),--(WEEKDAY(L1:L12)<>1))


so it can be used to subtract holidays if you are using the second formula

 

[Guest]

Thank you very much. This did exactly what i needed it to.

 

[Guest]

Try this:
=NETWORKDAYS((A1),(A2))+INT((A2-WEEKDAY(A2-6)-A1+8)/7)

 

[Guest]

or just

=INT((WEEKDAY(A1)+B1-A1)/7)

Try this:
=NETWORKDAYS((A1),(A2))+INT((A2-WEEKDAY(A2-6)-A1+8)/7)

 

[Guest]

Sorry, disregard previous reply

I note the above suggestions which use networkdays and then add a count of
Saturdays....but this will give an incorrect result if you have any Saturday
holidays within the period in question.

You need to use something like Peo's suggestion, i.e. count total days and
then subtract Sundays and non-Sunday holidays, i.e.

=B1-A1+1-INT((WEEKDAY(A1-1)+B1-A1)/7)-SUMPRODUCT(--(L1:L12>=A1),--(L1:L12<=B1),--(WEEKDAY(L1:L12)>1))

or just within one SUMPRODUCT formula

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))>1),--ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),L1:L12,0)))