Calculating End of Week Date

  • 2
  • 2
  • Question
  • Updated 1 year ago
  • Answered
I would like to amend the following formula so that the end result is always adjusted to be a following Friday date. Will appreciate some help. 

 If([Category] = "PNP", WeekdayAdd([Equip Reqd By],-21), 
WeekdayAdd([Equip Reqd By],-14))
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb

Posted 1 year ago

  • 2
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
Try this

var date RawDate = If([Category] = "PNP", WeekdayAdd([Equip Reqd By],-21), 
WeekdayAdd([Equip Reqd By],-14));

LastDayOfPeriod($RawDate, Weeks(1),Date(2017,5,20))

What does is to take the Period starting on a Saturday (it does not matter which Saturday) and based on a period length of 1 week, it calculates the Last Day of the Period, which of course must be a Friday if the 1 week period starts on a Saturday.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
This is a great solution.  Works.  I noticed  that you have used certain date (2017,5.20).  Will that need to be amended at any stage? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
That field will only needs to be changed if the world goes to a 6 or 8 day week as the new Global standard.  That seems unlikely any time soon!  So no, it can be any Saturday even one 200 years ago or in the future.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Haha  cheers