Calcualte Future or past date and....

Hello,
I need to have a user enter a date, in cell A1. this will be the launch date of a program.
I need to calculate when the training will need to start.
If the launch date is Oct 1, 2008, Excel will need to calculate that
August 1 - First group trained because it will be a1 - 56 days
Sept 1 - Second group trained because the date will be a1-28 days.

What I cant figure out is that I know the training needs to start 56 days and 28 days for the groups, but I need to find the closest Monday and provide that date.

So the first group would really start training Monday Aug 4th.
The second group would actually start training Sept 1, unless I can tell it that Monday Sept 1 is a holiday and I need Sept 2..

So in short, I just need to identify the closest Monday to the date and have Excel provide that. If it can take into account holidays, that is even a bonus.

Thanks

Hi,
Chip Pearson has a solution here under Next Day Of Week After A Date

Chee

Surely 56 days back from October 1st is 6th August? If you want the previous Monday there are more efficient versions of Chip's solution

=A1-56-WEEKDAY(A1-56,3)

Where A1 contains 1st October 2008

To make that pick the next workday if that Monday is a holiday you can extend that formula as follows:

=WORKDAY(A1-56-WEEKDAY(A1-56,3)-1,1,holidays)

Where holidays is a a named range with a list of your holiday dates

Note: WORKDAY is an Analysis ToolPak add-in function (unless you have Excel 2007)

Worked perfectly.
Thank you.

I would imaging that it is more difficult if the user does not ave the Analysis pack turned on? I have already had one person tell me that data is not available.

Rod

OK, I have a user that has 2003 and has turned on the Analysis pack but receives a #NUM error wen entering the date.

Can I use this formula in Excel 2003?
If not, what could I use?
Can I make this an executable to have someone use it as a stand alone?

Rod

If Analysis ToolPak isn't installed I'd expect the error to be #VALUE!. If the error is #NUM! check that A1 isn't blank.

Without Analysis ToolPak you could try

=ISNUMBER(MATCH(A1-56-WEEKDAY(A1-56,3),holidays,0))+A1-56-WEEKDAY(A1-56,3)

If the Monday is on the holidays list then this will move it to the Tuesday.....doesn't check if the Tuesday is a holiday, though........

Will try it with the user int eh AM and see.

in the formula, is it the 3 that relates to Monday and if so, how does it?

Originally Posted by rlcohen70

in the formula, is it the 3 that relates to Monday and if so, how does it?

In the original formula, i.e.

=A1-56-WEEKDAY(A1-56,3)

you get a Monday because WEEKDAY(date,3) returns a zero for a Monday a 1 for a Tues, a 2 for a Wed etc. If you wanted to return a Tuesday, for instance, you can add a -1, i.e.

=A1-56-WEEKDAY(A1-56-1,3)

If you wanted the previous Wednesday change the -1 to -2, etc.

Worked perfectly,
thank you.

Rod