SET SATURDAY AS WORKING DAY IN EXCEL
 

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT
("1:"&ABS(days)*10))))={2,3,4,5,6})*
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

change the array {2,3,4,5,6} to the weekdays that you want to use as working
days.

It uses three named ranges

start_date - single cell, obvious
days - single cell, the working days to project forward
holidays, range, an array of holiday dates

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" <Rohin wrote in message
...
HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

Hi

Write an UDF

I posted one as a reply to a thread Workdays from 29.05.2006 14:10 in
microsoft.public.excel newsgroup.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Rohin Bhatia" <Rohin wrote in message
...
HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say June 1.
How do I arrive at end date by considering 6 working days in the week - Thus
only Sunday is the off day . All other days including Saturday are working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

Rohin,

My original formula does exactly what you want, just change the days array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say June
1.
How do I arrive at end date by considering 6 working days in the week -
Thus
only Sunday is the off day . All other days including Saturday are
working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

Thanks a ton
Regds
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

Hi Bob
Sorry - coming back to u again.
The formula given by u works well in the case I gave before.
However if I have a target finish(End) date and know that a task requires
say X days,How do I work backwords to arrive at a start date - again using
same assumption that only Sunday + holiday list are nonworking - All other
days are working days.
Need ur help
Regds
Rohin

"Bob Phillips" wrote:

Rohin,

My original formula does exactly what you want, just change the days array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say June
1.
How do I arrive at end date by considering 6 working days in the week -
Thus
only Sunday is the off day . All other days including Saturday are
working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

Just use a negative number of days.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Hi Bob
Sorry - coming back to u again.
The formula given by u works well in the case I gave before.
However if I have a target finish(End) date and know that a task requires
say X days,How do I work backwords to arrive at a start date - again using
same assumption that only Sunday + holiday list are nonworking - All other
days are working days.
Need ur help
Regds
Rohin

"Bob Phillips" wrote:

Rohin,

My original formula does exactly what you want, just change the days
array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *

ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say
June
1.
How do I arrive at end date by considering 6 working days in the
week -
Thus
only Sunday is the off day . All other days including Saturday are
working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??

SET SATURDAY AS WORKING DAY IN EXCEL
 

Dear Bob
Thanks a lot.
Rohin

"Bob Phillips" wrote:

Just use a negative number of days.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Hi Bob
Sorry - coming back to u again.
The formula given by u works well in the case I gave before.
However if I have a target finish(End) date and know that a task requires
say X days,How do I work backwords to arrive at a start date - again using
same assumption that only Sunday + holiday list are nonworking - All other
days are working days.
Need ur help
Regds
Rohin

"Bob Phillips" wrote:

Rohin,

My original formula does exactly what you want, just change the days
array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *

ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say
June
1.
How do I arrive at end date by considering 6 working days in the
week -
Thus
only Sunday is the off day . All other days including Saturday are
working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??