Formula to Generate Which week of the year it is based on date entered in ""Process Date"" Field.

This may work for you, If you have a field which will have the start of the year, say "Year Start" then you can calculate how many days between Year Start and Process Date, divide by 7, to get the week.


Int(ToDays([Process Date] - [Year Start])/7 +1)

I wouldn't necessarily have a field for "year start"...but I have seen formulas where you can define the first day of the year and build the formula off of that but that formula was giving me syntax errors so I couldn't use it and am not familiar enough with formulas to fix it.

I can try to help you but would need to know what is the definition of the first day of the year?

The first day of the year would be 12/30/18 Sunday of the first week, which is week ending Saturday 1/5/19...it doesn't matter which date you go off of as long as it identifies that week as week 1.

I'm close with this function but this has the year starting on the literal Jan 1 2019-Tuesday when I need it to start Sunday 12/30/18 and the first week would end 1/5/19 
Int(DayOfYear([Process Date])/7+1)

This will work. 

The formula Ending Saturday of First Week of Year is

var date TodaysDate= [test today];

LastDayOfWeek(FirstDayOfYear($TodaysDate))

You would set up a field called test today to test various dates.  Then once you are satisfied, change the first formula variable to


var date TodaysDate= Today();


The there would be a separate field for the week number


var date TodaysDate= [test today];

var number WeeksSinceFirstWeek = 
(ToDays(LastDayOfWeek($TodaysDate)-[Ending Saturday of First Week of Year]))/7;

$WeeksSinceFirstWeek+1

I'm sorry I'm really new to QuickBase and formulas are one thing I really have a hard time understanding....would each formula above go in my weeks formula field or am I supposed to have the test today field still but rename to "FirstWeek" or something and put the first set of formulas there then the second set under the week formula? Then how do I incorporate the "Process Date" field as the field I am establishing the week for.... Or is the Process Date field supposed to be the "var date TodaysDate= [Process Date];"

That's what it looks like based on the other formula that doesn't have the right start date...not the new ones you are trying to help me understand...

I think that all you need to do is to change the formula from

LastDayOfWeek(FirstDayOfYear(Today()))

to

LastDayOfWeek(FirstDayOfYear([Process Date]))

That's what I tried but then 12/30/18 was coming up as week 53 instead of part of Week 1 in 2019.

I have a similar form, so I plugged in Mark's variables but made a slight adjustment to 'Week':

var number WeeksSinceFirstWeek = 

(ToDays(LastDayOfWeek([date])-[Ending Saturday of First Week of Year]))/7;

If($WeeksSinceFirstWeek =53,1,

$WeeksSinceFirstWeek+1)

It works when selecting past or future dates (process date), hope this helps

Actually, that didn't work so well - I eventually added another field called Week_numbers:

If([Week number]>52,1,[Week number])

to use in my report & on my form

So do you have a field for week that has this formula that tends to pull Week 53-

var number WeeksSinceFirstWeek = 

(ToDays(LastDayOfWeek([date])-[Ending Saturday of First Week of Year]))/7;

$WeeksSinceFirstWeek+1)

Then a [Week number] field with this formula to move week 53 to Week 1?

If([Week number]>52,1,[Week number]

Then just hide the Week field and display Week Number instead?

That worked :)

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.

Maybe I should create a [Report Date] formula-date field.  LastDayofWeek([Process Date)] 
Then change the [Ending Saturday of First Week of Year] formula to LastDayofWeek(FirstDayofYear([Report Date]))

Think that might work or add another field and still get the same result?

Yay, that worked! So that's my advice to anyone who looks at this thread :)