I am looking for a formula to calculate the "Pay Date" based off of date created

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

I can do this in Excel, just need to add it in QB. 

Excel Version

A1 - contains date created.

A2 Calculates the ending Wednesday of the week. All dates that fall between Thursday & Wednesday will have a "Pay Date" of that ending Wednesday.

Excel Formula - =A1-WEEKDAY(A1,14)+7

thanks for any help provided! 

Photo of Marc

Marc

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
Wel your lucky day.  QB has payroll functions for calculating payroll periods, like "every two weeks".

https://www.quickbase.com/db/6ewwzuuj?a=dr&r=bn&rl=kx


Try this

LastDayOfPeriod(ToDate([Date Created]),7, Date(2016,02,17))




Note that the last parameter can be any Wednesday. For example this would work too ... Date(2015,11,4) as Nov 4 2015 was also a Wednesday.
Photo of Marc

Marc

  • 0 Points
Thank you - This is actually what I came up with and believe it to be working. 



If(

ToDate([Date Created])>

LastDayOfWeek(ToDate([Date Created]))-Days(3),

LastDayOfWeek(ToDate([Date Created]))+Days(4),

LastDayOfWeek(ToDate([Date Created]))-Days(3))