Thanks:  0
Likes:  0

1. Hello,

I'm having a little problem calculating dates.

In a worksheet, I defined three tabs "Sent Letters", "Variables" and "Bank Holidays".

The following information is stored in the tab "Variables".

(A1) Letter (B1) Days to wait
(A2) 8 (B2) 20
(A3) 10 (B3) 30
(A4) 12 (B4) 40
(A5) 14 (B5) 50

The following information is stored in the tab "Sent Letters".

(A1) Letter (B1) Date Sent (C1) New Date
(A2) 8 (B2) 1-1-2002 (C2) (Formula)
(A3) .... (B3) .... (C3) (Formula)
(A4) .... (B4) .... (C4) (Formula)
(A5) .... (B5) .... (C5) (Formula)

In the tab "Bank Holidays" there are a number of dates of bank holidays, like
Easter and Christmas.

These dates are stored in the cells A1 to A20.

All entries are made in the tab "Sent Letters".

When a number is entered in cell A2, (for example "8") and a date
(for example "January 1st 2002") in cell B2, I want the following actions
to be done.

First the "Days to wait" are to be found (20 days) using the VLOOKUP function
on the tab "Variables".

The value found there has to be added to the date in cell B2 (on the tab "Sent Letters"),
and the result (January 21st 2002) has to be stored in cell C2.

No problem so far....

But of course there are a couple of weekends (Saturday's and Sunday's) "stored" in
these 20 days.

I do not want these weekends to be "counted". I only need "Working" days (Monday through Friday)
to be counted.

To make it even more complicated, there could also be some bank holidays (like Easter and Christmas)
"stored" in these "Days to wait".

All known holidays are stored in the tab "Holidays" in the same worksheet.

So what I want to do is the following.
When a "Letter Number" and a "Date Sent" is entered, the corresponding "Days to wait" has
to be found using the VLOOKUP function on the tab "Variables".

If there are weekends or holidays in the "Days to wait", they may not be "counted".

Could anyone please tell me if this is possible using "formulas" only ?
(I'm not a VBA programmer.)

Thanks in advance and best regards,

Frits Jager
(The Netherlands)

2. On 2002-05-03 05:07, Flash0220 wrote:

Hello,

I'm having a little problem calculating dates.

In a worksheet, I defined three tabs "Sent Letters", "Variables" and "Bank Holidays".

The following information is stored in the tab "Variables".

(A1) Letter (B1) Days to wait
(A2) 8 (B2) 20
(A3) 10 (B3) 30
(A4) 12 (B4) 40
(A5) 14 (B5) 50

The following information is stored in the tab "Sent Letters".

(A1) Letter (B1) Date Sent (C1) New Date
(A2) 8 (B2) 1-1-2002 (C2) (Formula)
(A3) .... (B3) .... (C3) (Formula)
(A4) .... (B4) .... (C4) (Formula)
(A5) .... (B5) .... (C5) (Formula)

In the tab "Bank Holidays" there are a number of dates of bank holidays, like
Easter and Christmas.

These dates are stored in the cells A1 to A20.

All entries are made in the tab "Sent Letters".

When a number is entered in cell A2, (for example "8") and a date
(for example "January 1st 2002") in cell B2, I want the following actions
to be done.

First the "Days to wait" are to be found (20 days) using the VLOOKUP function
on the tab "Variables".

The value found there has to be added to the date in cell B2 (on the tab "Sent Letters"),
and the result (January 21st 2002) has to be stored in cell C2.

No problem so far....

But of course there are a couple of weekends (Saturday's and Sunday's) "stored" in
these 20 days.

I do not want these weekends to be "counted". I only need "Working" days (Monday through Friday)
to be counted.

To make it even more complicated, there could also be some bank holidays (like Easter and Christmas)
"stored" in these "Days to wait".

All known holidays are stored in the tab "Holidays" in the same worksheet.

So what I want to do is the following.
When a "Letter Number" and a "Date Sent" is entered, the corresponding "Days to wait" has
to be found using the VLOOKUP function on the tab "Variables".

If there are weekends or holidays in the "Days to wait", they may not be "counted".

Could anyone please tell me if this is possible using "formulas" only ?
(I'm not a VBA programmer.)

Thanks in advance and best regards,
Frits Jager
(The Netherlands)
In C2 in Sent Letters enter and copy down:

=WORKDAY(B2,VLOOKUP(B2,Variables!\$A\$2:\$B\$100,2,0),'Bank Holidays'!\$A\$2:\$A\$20)

Note. WORKDAY is only available if the Analysis Toolpak add-in is activated (via Tools|Add-Ins).

3. Hello,

It works !

Best regards,
Frits Jager

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•