Formula to Generate Which week of the year it is based on date entered in ""Process Date"" Field.
In search of Formula to Generate Which week of the year it is based on date entered in "Process Date" Field. I tried one and it is telling me how many weeks it has bee since that week instead of the w...
This has been working really well, thought all the kinks were worked out but now it's quirky again. So if the Process Date (which is in the future in this case) is 12/30/19 the ending Saturday should switch to 1/4/2020. The ending Saturday is calculated off of the Process Date so I don't no why it is static.
As a recap.... I have a [Week Number] field that has this formula:
var number WeeksSinceFirstWeek = (ToDays(LastDayOfWeek([Process Date])-[Ending Saturday of First Week of Year]))/7;
$WeeksSinceFirstWeek+1
I have a [Week] field that has this formula...
If([Week Number]>52,1,[Week Number])
[Process Date] is a regular Date Field that is usually Today() but I had to back enter a lot of data so I do not put that in as a formula.
My [Year] field:
Year([Ending Saturday of First Week of Year])
And [Ending Saturday of First Week of Year]:
LastDayOfWeek(FirstDayOfYear([Process Date]))
Is it my [Year] field? Does it need something to designate that it is referring to the [Ending Saturday First Week of the Year] for the process date...It has trouble understanding that I want the year to be the year for the ending Saturday not the process date. In the image below 12/30/18 is supposed to be the 1st day of the year for 2019 reporting so the week is good but the year is wrong.
