Weekly budget dates

[schalkj]

I am developing a manament reporting tool. The report covers 52 weeks, and there are 5 changing periods:


1. Reporting is done for each week ending on the Friday

2. The week's reporting ends on the 25th if the 25th falls on onany weekday other than a Friday

3. If the 25th falls on a Saturday or Sunday, theweek ends on the last Friday before the 25th

4. In December the month ends on the 15th, not the 25th

5. Our offices are closed between 25th Dec and 1 Jan, so that period must not be included in the weekly reporting.

6. We have differenet companies with different yearends. For the last month of the the year the week must end on the calender monthend.


Can I use formulas in stead of multiple nested IF statements to set the closing dates for each week? As each company's financial year starts in a different month, the weekly dates differ as per above.


Any help in this regard?

 

[Hui]

You could use standard formulas and for your dates look them up from a table of monthly dates elsewhere, rather than calculate them everytime in each formula

 

[vijaySharma]

I second Hui here, create a master sheet with all the dates and then Lookup them where required.


The task is easily maintainable and you would not have to go through complex formulas.


~Vijay