Hi folks...
Got a bit of a mental block on this one. Wonder if anyone has any ideas or has had to do anything similar with their projects and has any info they would be willing to share?
What I want:
To create a function to calculate the current period and week number based on the current date, for any given date on any given year.
What I know:
The year is split into 12 periods.
Periods 3, 6, 9 and 12 have 5 7-day weeks, while the others have 4 7-day weeks. This is a total of 364 days per financial year.
1st April should always be in Week 1, Period 1.
To stop the start of the year rolling back by a day or two every year, if the end of that fiscal year is due to end on 24th or 25th March, an extra week is added to period 12 - this normally happens roughly every 6 years.
The financial year (period and weeks, too) always start on a sunday and end on a saturday.
--
I'd love to be able to enter a date and have the system tell me which week and which period the date is in. But both myself and one of the other guys in the office are having a complete brain-freeze on this.
Anyone give me any pointers?
Ad