# Dynamic week formula

• 0
• Question
• Updated 4 years ago

I need a formula that will dynamically change as time goes on and shows what week a previous [Date Created] will fall in. For example, today is 11/3, so any day between 11/1 and 11/3 would be week 1, any day between 10/25 - 10/31 would be week 2, any day between 10/18 - 10/24 would be week 3, etc.

If today was 11/10 the the dates between 11/8 - 11/10 would now be week 1, 11/1 - 11/7 would be week 2, etc.

I need to identify at least 6 rolling weeks. I don't necessarily need the word "weeks" included; just a way to differentiate between weeks 1 - 6. They can be numeric/text, doesn't matter.

Thanks for the help.

• 0 Points

Posted 4 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 67,448 Points
There are probably many ways to do this, but try this as a formula numeric field

If(
FirstDayOfWeek(Today()-Weeks(0)) = FirstDayOfWeek([Date]),1,
FirstDayOfWeek(Today()-Weeks(1)) = FirstDayOfWeek([Date]),2,
FirstDayOfWeek(Today()-Weeks(2)) = FirstDayOfWeek([Date]),3,
FirstDayOfWeek(Today()-Weeks(3)) = FirstDayOfWeek([Date]),4,
FirstDayOfWeek(Today()-Weeks(4)) = FirstDayOfWeek([Date]),5,
FirstDayOfWeek(Today()-Weeks(5)) = FirstDayOfWeek([Date]),6,
FirstDayOfWeek(Today()-Weeks(6)) = FirstDayOfWeek([Date]),7)
• 0 Points
I had to make some modifications, but this is exactly what I needed (I might have made too many conversions but it did work):

If(
ToText(FirstDayOfWeek(Today()-Weeks(0))) = ToText(FirstDayOfWeek(ToDate([Date Created]))),1,
ToText(FirstDayOfWeek(Today()-Weeks(1))) = ToText(FirstDayOfWeek(ToDate([Date Created]))),2,
ToText(FirstDayOfWeek(Today()-Weeks(2))) = ToText(FirstDayOfWeek(ToDate([Date Created]))),3,
ToText(FirstDayOfWeek(Today()-Weeks(3))) = ToText(FirstDayOfWeek(ToDate([Date Created]))),4,
ToText(FirstDayOfWeek(Today()-Weeks(4))) = ToText(FirstDayOfWeek(ToDate([Date Created]))),5,
ToText(FirstDayOfWeek(Today()-Weeks(5))) = ToText(FirstDayOfWeek(ToDate([Date Created]))),6,
ToText(FirstDayOfWeek(Today()-Weeks(6))) = ToText(FirstDayOfWeek(ToDate([Date Created]))),7)

Thank you very much for the help.

QuickBaseCoach App Dev./Training, Champion

• 67,448 Points
Thx for letting me know!