6 day/week function ie:networkday
 

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.

6 day/week function ie:networkday
 

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

"Robert" wrote:

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.

6 day/week function ie:networkday
 

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


--
Regards,

Peo Sjoblom



"Robert" wrote in message
...
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.

6 day/week function ie:networkday
 

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

"Toppers" wrote:

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

"Robert" wrote:

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.

6 day/week function ie:networkday
 

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




"Robert" wrote:

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.

6 day/week function ie:networkday
 

or just

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

"RyGuy" wrote:

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




"Robert" wrote:

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.

6 day/week function ie:networkday
 

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

"daddylonglegs" wrote:

or just

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

"RyGuy" wrote:

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




"Robert" wrote:

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.