Calculate Week and Days in a Week: Not a straight forward logic
Hope you are doing great!
I have to create a peculiar way to calculate weeks and then calculate the days in each of these weeks. Please find below the rules to create a week-
- Weeks will be defined from Sunday to Saturday. The only exception to this rule is stated in rule(4)
- The usual number of days in a week are 7 days in cases where the Start Date and the End Date of the week lie in the same month
- The only exception to rule(2) is in case we move from 1 month to the other while calculating a week.
- From the 1st date of any month, it is mandatory to start a new week irrespective of what day it is.
- The total number of days in a week cannot be less than 5 ever. This rule should never be violated
Let us look at the example below for January 2019 and September 2019 to better understand the way weeks and days in a week are calculated -
Days in Week
30-Sep-2019 (Mon)(End of the old month - if we don't go till 31st, the next week will have less than 5 days)
01-Oct-2019 (First day of the new month)
Days in Week
31/01/2019 (End of the old month)
01/02/2019 (First day of the new month)
09/01/2019 (2nd Sat as if we take the 1st Saturday, the week would have less than 5 days)
What I have done -
I have created user input to input the start date of this week calculation logic.
I have created a list with members like 22.214.171.124.5.6.7……..52 weeks in them
Start Date of [Week 2] will be the End Date[Week 1]+1
Another line item that calculates Start Date + 7 days
If the months of "Start Date" and "Start Date+7" are different that shows we are month end, so I have added additional logic
But then I get a circular Reference with End Date as my End Date formula again involves Start Date
Any support in this regard will be highly appreciated