Forum Discussion
Custom Weeks for Seasonal Events
Hi rtiwari,
If you only consider with specific days instead of fact week numbers, I'd like to suggest you use DATEDIFF function to calculate get diff days from start date, end date and divide with period number(7) to get custom week number.
After these steps, you can use a number with MOD function to process with above result week number to convert them into the ranges.
Notice: the start date, end date may across many years will get a number over year week numbers limits, mod function will help convert them into the available range.
Regards,
Xiaoxin Sheng
Thank you Xiaoxin for your help. As you already mentioned that the WEEKNUM function may not be the right formula for this situation as it resets at calendar year end. I would be happy to call the desired week column rather as a 'Phases' of a campaign, if that helps. The 'Week' idea was just to have an equivalent period for the season across the various year of the event. I could even add this information in the source data itself if there was an Excel formula that could do the same i.e. allocate 7 days from the registration start date as a Phase -1 and allocate the 8th day from the registration start date as Phase 2 and so on. It will eventually reset the Phase count once the Year-Season column value changes to the next season.
I'm thinking maybe I could have a CALENDARAUTO table based on MIN and MAX registration dates and then add a column that clubs the 7 days as a Phase 1 and goes on. Or the longer way of having different Calendars for various years and eventually appending them all together.
Hi rtiwari,
If you only consider with specific days instead of fact week numbers, I'd like to suggest you use DATEDIFF function to calculate get diff days from start date, end date and divide with period number(7) to get custom week number.
After these steps, you can use a number with MOD function to process with above result week number to convert them into the ranges.
Notice: the start date, end date may across many years will get a number over year week numbers limits, mod function will help convert them into the available range.
Regards,
Xiaoxin Sheng