calculating a date with the CHOOSE function

[Jen C]

Hi,

I am looking for some help calculating a date. Say I have a start date
in cell A1. I need to calculate a date 30 days in the future (including
weekends), however, if the last day lands on a weekend or holiday then
I need the result to move to the first workday.
For example:
A1: Aug 18, 2006
30 days from Aug 18, 2006 is Sept 17, 2006. Because this is a Sunday, I
need the result to be the next working day: Sept 18, 2006.

Thank you for your help!

Jen

 

[Guest]

Hi

Not sure exactly what the Choose function relates to. But from what you
describe you should be able to use the following:

=WORKDAY(A1,(B1-((B1/7)*2)))

This works with work days only (although you must specify holidays if you
want it to take these into account), I have just used the 30 days and that
amount less the weekend days gives the amount of working days in the future
to return.

Where A1 is the begining date and B1 is the days in the future you want the
date for.


Hope this helps.

Nav

 

[Jen C]

Great, thank you very much.

I assumed I was to use the Choose function because I found a formula to
calculate 20 days from a date that looked like this:
=20+CHOOSE(WEEKDAY(A1),1,0,0,0,0,0,2)+A1
However, I was unable to determine what the 7 digits (1,0,0,0,0,0,2)
represented and the reason for them.

Thanks once again.
Jen

 

[Guest]

No problem, glad it worked. FYI: Choose is more for when you want to pick
something in a particular list, ie. the nth item in Mon, Tue, Wed...

I would appreciate it if you could rate the post if the answer solves your
problem whenever you ask a question on these groups.

Regards, Nav

 

[Jen C]

Sorry, rated your post. Thanks once again!

No problem, glad it worked. FYI: Choose is more for when you want to pick
something in a particular list, ie. the nth item in Mon, Tue, Wed...

I would appreciate it if you could rate the post if the answer solves your
problem whenever you ask a question on these groups.

Regards, Nav