Problem calculating dates

Flash0220

Board Regular
Joined
May 2, 2002
Messages
104
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)
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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).
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top