Hi, I need to identify the week which shares days between the month and also calculate the no. of. days accounted for each month
EG: 2023-01-30 Week (Monday is the week start)
from the above example I need to identify the week which shares days between the two months and calculate days accounted for each month.
here : January has 2 days and February has 5 days.
The first thing I did was use the Formula tool to determine the week number and month number for each date. I then used a Summarize tool configured to Group by week number and month number to remove duplicates. Then I used another Summarize tool to concatenate the month number for each week number then filtered out only the records where the Concat_MonthNum contains a comma. Finally, I joined this back to the original data on the week number. The output identifies the dates where the week overlaps from month to month.