Hello,
I'm having the following problem.
I have a (Excel Office 2003) spreadsheet, containing the following
information.
On a sheet called "Invoerscherm" (sorry, that's dutch) I have a column G
which contains the following formula :
=VERT.ZOEKEN(C168;Variabelen!$B$5:$E$20;2;ONWAAR)+E168
This formula calculates a date, on this date our company has to take
some kind of action such as calling a customer or sending them a letter.
But the problem is that sometimes the formula calculates a date which
is an official holliday like christmas, easter or other day's like that.
Since our company is closed on these dates the call or letter to be sent has to be done on a workday before that particular calculated date.
So if the calculated date is december 25th 2006 (Chrismas) then
the formula has to be modified in some way, that friday december 22nd 2006 is calculated instead of december 25th.
If the calculated date is december 26th 2006 (Also christmas), the same
date (december 22nd) has to be calculated.
Both christmas day's and the weekend are holidays, so these dates should
not be generated by the (modified) formula.
I already have a list with official dutch holidays defined in range A7:A47
on a sheet (tab) called "Feestdagen".
Is there a way to modify the formula mentioned above, to do this ?
Best regards,
Frits Jager
The Netherlands
I'm having the following problem.
I have a (Excel Office 2003) spreadsheet, containing the following
information.
On a sheet called "Invoerscherm" (sorry, that's dutch) I have a column G
which contains the following formula :
=VERT.ZOEKEN(C168;Variabelen!$B$5:$E$20;2;ONWAAR)+E168
This formula calculates a date, on this date our company has to take
some kind of action such as calling a customer or sending them a letter.
But the problem is that sometimes the formula calculates a date which
is an official holliday like christmas, easter or other day's like that.
Since our company is closed on these dates the call or letter to be sent has to be done on a workday before that particular calculated date.
So if the calculated date is december 25th 2006 (Chrismas) then
the formula has to be modified in some way, that friday december 22nd 2006 is calculated instead of december 25th.
If the calculated date is december 26th 2006 (Also christmas), the same
date (december 22nd) has to be calculated.
Both christmas day's and the weekend are holidays, so these dates should
not be generated by the (modified) formula.
I already have a list with official dutch holidays defined in range A7:A47
on a sheet (tab) called "Feestdagen".
Is there a way to modify the formula mentioned above, to do this ?
Best regards,
Frits Jager
The Netherlands