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" wrote in message
...

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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
ah ha ! - i'll give that a go !

Cheers
Andy

"Trevor Shuttleworth" wrote in message
...
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" wrote in message
...

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