R
Rohin Bhatia
HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ??
DAY ??
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+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)
("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)
A
Arvi Laanemets
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.
Write an UDF
I posted one as a reply to a thread Workdays from 29.05.2006 14:10 in
microsoft.public.excel newsgroup.
R
Rohin Bhatia
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
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
B
Bob Phillips
Rohin,
My original formula does exactly what you want, just change the days array
=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+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)
My original formula does exactly what you want, just change the days array
=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+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)
R
Rohin Bhatia
Thanks a ton
Regds
Rohin
Regds
Rohin
R
Rohin Bhatia
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
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
B
Bob Phillips
Just use a negative number of days.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
R
Rohin Bhatia
Dear Bob
Thanks a lot.
Rohin
Thanks a lot.
Rohin
Bob Phillips said:Just use a negative number of days.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)