Discussions

Expand all | Collapse all

Calculating End of Week Date

  • 1.  Calculating End of Week Date

    Posted 05-17-2017 05:55
    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))


  • 2.  RE: Calculating End of Week Date

    Posted 05-17-2017 11:26
    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.


  • 3.  RE: Calculating End of Week Date

    Posted 05-17-2017 21:56
    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? 


  • 4.  RE: Calculating End of Week Date

    Posted 05-17-2017 22:13
    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.


  • 5.  RE: Calculating End of Week Date

    Posted 05-17-2017 22:57
    Haha  cheers