Requirement
When a date is entered e.g 1/27/2007 The bill payment dates should all be automatically calculated under the “Date” column
Rules for the date
Rule 1. e.g. 1 Month—the date should be 1 month after the date entered (as shown by the simple example 1/27/2007 one month is 2/27/2007 in the sheet)
Rule2 if the date entered is the last day of the month, the calculated date should also be the last day of the month provided it is a working day.
E.g 1/31/2007 one month is 2/28/2007. (provided 2/28 is working day)
2/28/2007 one month is 4/30/2007 (provided 4/30 is working day)
Rule3 If the calculated date based on rule1 and rule2 is a non-working day, the date is pushed back by 1 till it becomes a working day.
e.g 1/27/2007 two month is 3/27/2007 if 3/27/2007 is Saturday it becomes 3/28/2007 which is a Sunday, another non-working day. So the output should be 3/29/2007
Rule4, under rule3, the date cannot be pushed to the next month. Instead, we push the date forward. In the previous example assume 3/29, 3/30 and 3/31 are all public holidays, by rule3, it will be finally pushed to 4/1. But this becomes another month. So we push forward instead to 3/26 which is Friday. The output is 3/26/2007
Definition of working dates (Monday to Friday excluding the holidays listed in the sheet)
Important Note
1. Date functions are available only if the Analysis ToolPak add-in program is installed. If the add-in is not installed, you may see the #NAME? error value. To find out if the add-in is installed, on the Tools menu, click Add-Ins. Analysis ToolPak and Analysis ToolPak VBA and should have a check mark in the box beside it. If it doesn't, just click to select that box, click OK, and follow instructions.
2. Remember to format cells as dates
3. You can use excel functions and/or macro to achieve the output. However, bonus will be given to those without using macro.
Additional Bonus Work
You are strongly encouraged to check out the holiday dates of 2008, 2009…2015 inline with those listed in 2007, and you can incorporate them into the worksheet. You can also increase the number of months to a maximum of 24 months for the ultimate bonus.
Work Sheet Download Address
http://www.wikiupload.com/download_page.php?id=63818
click "Download" on the write. Enter verification code shown and press "Get"
