How to Calculate Fiscal Week.
Posted on 2005-04-28
I am looking for a way to calculate the current fiscal week.
Our fiscal Year Technically starts in Feb. But specifically it ends on the sun closes to jan 31st whether that day falls before or after Feb and starts that following Monday.
2003: Began Monday Feb 3rd
2003: Ended Sun Feb 1st
2004: Started Mon Feb 2nd
2004: End Sun Jan 30th
2005: Started Mon Jan31st
2005: Ended Sun Jan 29th
I have a formula the will calculate the fiscal week if the fiscal year starts in Feb 2nd.
DatePart("ww", Now() - 31, vbSunday, vbFirstJan1)
the above function returns 14 and it needs to return 13
I need a function that can give me the fiscal week if I supply a start date and or end date or a similar solution.
I also need a calculation that will give me:
1. the last Sunday in Jan
2. the closest Monday to Feb 1st (regardless if it's in Feb or Jan)