Problems with date calculations (bank hols etc)

I have a database of dates of claims received.

Column A has the customers name, Column B is the date claim was received.

Problem is that in column C i want to enter the 60th day (i.e. Whatever is
in column B + 60days - however this MUST NOT include Saturdays or Sundays in
the calculation and should also NOT include bank holidays). I have a
separate sheet of all the bank holidays listed. I haven't a clue how to
solve this one !. Please help. So for e.g Mr Smith puts his claim in on 16th
March 2005, i want column C to add 60 WORKING days onto this, but miss out
the bank holidays (i.e. 25/4, 28/4, 2/5 and 30/5), the answer i get should
be 13/6/05. Bearing in mind that the date of receipt of the claim actually
counts as 1 day also.

Please help !

Andy

Andy

you need to use the WORKDAY function from the Analysis Toolpack.

You'll probably need to add 1 to the calculation to exclude the current date
too

From the Help:

WORKDAY



Returns a number that represents a date that is the indicated number of
working days before or after a date (the starting date). Working days
exclude weekends and any dates identified as holidays. Use WORKDAY to
exclude weekends or holidays when you calculate invoice due dates, expected
delivery times, or the number of days of work performed.



If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.



How?



On the Tools menu, click Add-Ins.

In the Add-Ins available list, select the Analysis ToolPak box, and then
click OK.

If necessary, follow the instructions in the setup program.

Syntax



WORKDAY(start_date,days,holidays)



Important Dates should be entered by using the DATE function, or as results
of other formulas or functions. For example, use DATE(2008,5,23) for the
23rd day of May, 2008. Problems can occur if dates are entered as text.



Start_date is a date that represents the start date.



Days is the number of nonweekend and nonholiday days before or after
start_date. A positive value for days yields a future date; a negative value
yields a past date.



Holidays is an optional list of one or more dates to exclude from the
working calendar, such as state and federal holidays and floating holidays.
The list can be either a range of cells that contain the dates or an array
constant of the serial numbers that represent the dates.



Regards



Trevor





"Andy100" <[email protected]> wrote in message
news:[email protected]...

>I have a database of dates of claims received.
>
> Column A has the customers name, Column B is the date claim was received.
>
> Problem is that in column C i want to enter the 60th day (i.e. Whatever is
> in column B + 60days - however this MUST NOT include Saturdays or Sundays
> in
> the calculation and should also NOT include bank holidays). I have a
> separate sheet of all the bank holidays listed. I haven't a clue how to
> solve this one !. Please help. So for e.g Mr Smith puts his claim in on
> 16th
> March 2005, i want column C to add 60 WORKING days onto this, but miss out
> the bank holidays (i.e. 25/4, 28/4, 2/5 and 30/5), the answer i get should
> be 13/6/05. Bearing in mind that the date of receipt of the claim actually
> counts as 1 day also.
>
> Please help !
>
> Andy
>
>

ah ha ! - i'll give that a go !

Cheers
Andy

"Trevor Shuttleworth" <[email protected]> wrote in message
news:[email protected]...
> Andy
>
> you need to use the WORKDAY function from the Analysis Toolpack.
>
> You'll probably need to add 1 to the calculation to exclude the current
date
> too
>
> From the Help:
>
> WORKDAY
>
>
>
> Returns a number that represents a date that is the indicated number of
> working days before or after a date (the starting date). Working days
> exclude weekends and any dates identified as holidays. Use WORKDAY to
> exclude weekends or holidays when you calculate invoice due dates,
expected
> delivery times, or the number of days of work performed.
>
>
>
> If this function is not available, and returns the #NAME? error, install
and
> load the Analysis ToolPak add-in.
>
>
>
> How?
>
>
>
> On the Tools menu, click Add-Ins.
>
> In the Add-Ins available list, select the Analysis ToolPak box, and then
> click OK.
>
> If necessary, follow the instructions in the setup program.
>
> Syntax
>
>
>
> WORKDAY(start_date,days,holidays)
>
>
>
> Important Dates should be entered by using the DATE function, or as
results
> of other formulas or functions. For example, use DATE(2008,5,23) for the
> 23rd day of May, 2008. Problems can occur if dates are entered as text.
>
>
>
> Start_date is a date that represents the start date.
>
>
>
> Days is the number of nonweekend and nonholiday days before or after
> start_date. A positive value for days yields a future date; a negative
value
> yields a past date.
>
>
>
> Holidays is an optional list of one or more dates to exclude from the
> working calendar, such as state and federal holidays and floating
holidays.
> The list can be either a range of cells that contain the dates or an array
> constant of the serial numbers that represent the dates.
>
>
>
> Regards
>
>
>
> Trevor
>
>
>
>
>
> "Andy100" <[email protected]> wrote in message
> news:[email protected]...
>
> >I have a database of dates of claims received.
> >
> > Column A has the customers name, Column B is the date claim was
received.
> >
> > Problem is that in column C i want to enter the 60th day (i.e. Whatever
is
> > in column B + 60days - however this MUST NOT include Saturdays or
Sundays
> > in
> > the calculation and should also NOT include bank holidays). I have a
> > separate sheet of all the bank holidays listed. I haven't a clue how to
> > solve this one !. Please help. So for e.g Mr Smith puts his claim in on
> > 16th
> > March 2005, i want column C to add 60 WORKING days onto this, but miss
out
> > the bank holidays (i.e. 25/4, 28/4, 2/5 and 30/5), the answer i get
should
> > be 13/6/05. Bearing in mind that the date of receipt of the claim
actually
> > counts as 1 day also.
> >
> > Please help !
> >
> > Andy
> >
> >
>
>

Is there another way, because in order to click "Analysis Toolpack" it asks
me for the original CD-ROM, which work say they haven't got, or more likely
they don't know where it's kept !

Cheers
Andrew


"Andy100" <[email protected]> wrote in message
news:[email protected]...
> ah ha ! - i'll give that a go !
>
> Cheers
> Andy
>
> "Trevor Shuttleworth" <[email protected]> wrote in message
> news:[email protected]...
> > Andy
> >
> > you need to use the WORKDAY function from the Analysis Toolpack.
> >
> > You'll probably need to add 1 to the calculation to exclude the current
> date
> > too
> >
> > From the Help:
> >
> > WORKDAY
> >
> >
> >
> > Returns a number that represents a date that is the indicated number of
> > working days before or after a date (the starting date). Working days
> > exclude weekends and any dates identified as holidays. Use WORKDAY to
> > exclude weekends or holidays when you calculate invoice due dates,
> expected
> > delivery times, or the number of days of work performed.
> >
> >
> >
> > If this function is not available, and returns the #NAME? error, install
> and
> > load the Analysis ToolPak add-in.
> >
> >
> >
> > How?
> >
> >
> >
> > On the Tools menu, click Add-Ins.
> >
> > In the Add-Ins available list, select the Analysis ToolPak box, and then
> > click OK.
> >
> > If necessary, follow the instructions in the setup program.
> >
> > Syntax
> >
> >
> >
> > WORKDAY(start_date,days,holidays)
> >
> >
> >
> > Important Dates should be entered by using the DATE function, or as
> results
> > of other formulas or functions. For example, use DATE(2008,5,23) for the
> > 23rd day of May, 2008. Problems can occur if dates are entered as text.
> >
> >
> >
> > Start_date is a date that represents the start date.
> >
> >
> >
> > Days is the number of nonweekend and nonholiday days before or after
> > start_date. A positive value for days yields a future date; a negative
> value
> > yields a past date.
> >
> >
> >
> > Holidays is an optional list of one or more dates to exclude from the
> > working calendar, such as state and federal holidays and floating
> holidays.
> > The list can be either a range of cells that contain the dates or an
array
> > constant of the serial numbers that represent the dates.
> >
> >
> >
> > Regards
> >
> >
> >
> > Trevor
> >
> >
> >
> >
> >
> > "Andy100" <[email protected]> wrote in message
> > news:[email protected]...
> >
> > >I have a database of dates of claims received.
> > >
> > > Column A has the customers name, Column B is the date claim was
> received.
> > >
> > > Problem is that in column C i want to enter the 60th day (i.e.
Whatever
> is
> > > in column B + 60days - however this MUST NOT include Saturdays or
> Sundays
> > > in
> > > the calculation and should also NOT include bank holidays). I have a
> > > separate sheet of all the bank holidays listed. I haven't a clue how
to
> > > solve this one !. Please help. So for e.g Mr Smith puts his claim in
on
> > > 16th
> > > March 2005, i want column C to add 60 WORKING days onto this, but miss
> out
> > > the bank holidays (i.e. 25/4, 28/4, 2/5 and 30/5), the answer i get
> should
> > > be 13/6/05. Bearing in mind that the date of receipt of the claim
> actually
> > > counts as 1 day also.
> > >
> > > Please help !
> > >
> > > Andy
> > >
> > >
> >
> >
>
>