Past Date Calculation
 

I have a difficult problem to solve. I am working with Excel 2003 and trying
to calculate a prior date based on a particular billing date. Normally this
would be solved with the workday function but I have a few special
conditions.

The first is a range of holidays that if the result equals one of those
holidays, then I want the date to readout the prior business day (for
example, since September 1 is a banking holiday and the result should then be
August 29).

The second condition is our holidays. The calculation needs to omit those
days from consideration (for example, if the billing date is Dec 1 and we
have Nov 27 & 28 off, the result should be Nov 24.)

The third condition is that these two lists of holidays have some overlap.

Let me know if I didn't explain clearly or any other information I need to
provide to arrive at a solution. Currently the people are hand counting by
the calendar each month introducing a high risk for error. Please advise.

Past Date Calculation
 

So how many days are we talking about here?

Note that you can specify a range and put all the holidays in there

=WORKDAY (date,-5,Holidays)



--


Regards,


Peo Sjoblom

"debinnyc" wrote in message
...
I have a difficult problem to solve. I am working with Excel 2003 and
trying
to calculate a prior date based on a particular billing date. Normally
this
would be solved with the workday function but I have a few special
conditions.

The first is a range of holidays that if the result equals one of those
holidays, then I want the date to readout the prior business day (for
example, since September 1 is a banking holiday and the result should then
be
August 29).

The second condition is our holidays. The calculation needs to omit those
days from consideration (for example, if the billing date is Dec 1 and we
have Nov 27 & 28 off, the result should be Nov 24.)

The third condition is that these two lists of holidays have some overlap.

Let me know if I didn't explain clearly or any other information I need to
provide to arrive at a solution. Currently the people are hand counting by
the calendar each month introducing a high risk for error. Please advise.

Past Date Calculation
 

Each list has ten days. However the criteria for what I want the formula to
do is different for each list. And the lists overlap. Therein lies the
problem.

"Peo Sjoblom" wrote:

So how many days are we talking about here?

Note that you can specify a range and put all the holidays in there

=WORKDAY (date,-5,Holidays)



--


Regards,


Peo Sjoblom

"debinnyc" wrote in message
...
I have a difficult problem to solve. I am working with Excel 2003 and
trying
to calculate a prior date based on a particular billing date. Normally
this
would be solved with the workday function but I have a few special
conditions.

The first is a range of holidays that if the result equals one of those
holidays, then I want the date to readout the prior business day (for
example, since September 1 is a banking holiday and the result should then
be
August 29).

The second condition is our holidays. The calculation needs to omit those
days from consideration (for example, if the billing date is Dec 1 and we
have Nov 27 & 28 off, the result should be Nov 24.)

The third condition is that these two lists of holidays have some overlap.

Let me know if I didn't explain clearly or any other information I need to
provide to arrive at a solution. Currently the people are hand counting by
the calendar each month introducing a high risk for error. Please advise.