Hi,
I need to calculate the month based on the no. of days in a week.
Example:
We are good with week-2 of may since all the days inside the month of may.
But if you see the 5th of week of may where it has 3 days from May and first 2 days from June.
I want to consider the entire 5th week of may to assigned as month of may.
So, the condition i require to use is whenever a week contains => 3 days from a month then it is assigned to the particular month.
Things to consider, the date field contains only the Monday of every week. So, with having only one date from a week we need to assign the month according to the above condition.
Can someone me help me here? Thanks in advance
Would it be safe to say that whatever month the Wednesday falls in would be the month you want associated with the week?
You can adjust the date of the Monday to be Wed and then check the month.
DateTimeMonth(DateTimeAdd([date],2,"Days"))
@SPetrie tried to use the formula you provided it works for some months but
But if you see below, the dates below should the month_! of 5 whereas it is showing 6 from Tuesday. Thanks in advance
I based the formula off this statement in the original question.
Things to consider, the date field contains only the Monday of every week. So, with having only one date from a week we need to assign the month according to the above condition.
In the examples you are showing, you are using every day of the week. Do you need it to do this calculation for every day or only for Mondays as originally asked?
There are ways of doing both, but I am confused about what you are actually wanting now.
@Ronal_bal - I am facing a similar issue and would like to know if you managed to solve it?
I would use the TS filler tool to get list of all dates within the month(s) you would like.
Then use the formula tool with the following two expressions
DateTimeMonth([date field]) this will give you the month as an output
and then
datetime parse( [date field], "%A") this will give you what day of the week it is
Then use the summarise tool, group by month and day of the week (both created in the above formula tool) and then count. This will give you the number of days in a month and then do further manipultation/fitlering as required.
@D11ERX @Ronal_bal hope this helps
Thank you @jdminton. This is just what I was looking for! 👍
@D11ERX great! please mark the solution!