How to set Saturday as a working day
 

I want to use Excel to calculate leave. As Saturday is a working day, it is
not possible to use Networkdays and workdays function. Anyway to make
Saturday as a working day?

If you want to just advance by 1 day, that is Sun and Mon are non-working
days, just take 1 from each day (including any holiday list)

=networkdays(start_date-1,end_date-1)

If you want to have one less day off per week, then use

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2) ={1,2,3,4,5,
6}))
-SUMPRODUCT(--(COUNTIF(holidays,(ROW(INDIRECT(start_date&":"&end _date)))*(WE
EKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1 ,2,3,4,5,6}))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chatter_tk" wrote in message
...
I want to use Excel to calculate leave. As Saturday is a working day, it
is
not possible to use Networkdays and workdays function. Anyway to make
Saturday as a working day?

Hi Bob,

Really thanks for your help. But I am rather new to excel and couldn't
understand it fully. I have two column that contain the start_date and
end_date. But when I replaced it using your solution, I got error.

Do you have any other alternative?


"Bob Phillips" wrote:

If you want to just advance by 1 day, that is Sun and Mon are non-working
days, just take 1 from each day (including any holiday list)

=networkdays(start_date-1,end_date-1)

If you want to have one less day off per week, then use

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2) ={1,2,3,4,5,
6}))
-SUMPRODUCT(--(COUNTIF(holidays,(ROW(INDIRECT(start_date&":"&end _date)))*(WE
EKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1 ,2,3,4,5,6}))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chatter_tk" wrote in message
...
I want to use Excel to calculate leave. As Saturday is a working day, it
is
not possible to use Networkdays and workdays function. Anyway to make
Saturday as a working day?

Which of the options that I offered applies here?

What are the columns, and what rows are used?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chatter_tk" wrote in message
...
Hi Bob,

Really thanks for your help. But I am rather new to excel and couldn't
understand it fully. I have two column that contain the start_date and
end_date. But when I replaced it using your solution, I got error.

Do you have any other alternative?


"Bob Phillips" wrote:

If you want to just advance by 1 day, that is Sun and Mon are
non-working
days, just take 1 from each day (including any holiday list)

=networkdays(start_date-1,end_date-1)

If you want to have one less day off per week, then use


=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2) ={1,2,3,4,5,
6}))

-SUMPRODUCT(--(COUNTIF(holidays,(ROW(INDIRECT(start_date&":"&end _date)))*(
WE
EKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1 ,2,3,4,5,6}))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chatter_tk" wrote in message
...
I want to use Excel to calculate leave. As Saturday is a working day,
it
is
not possible to use Networkdays and workdays function. Anyway to make
Saturday as a working day?