Dynamic week formula

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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. 

Photo of Daniel

Daniel

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
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)
Photo of Daniel

Daniel

  • 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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Thx for letting me know!