Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
2 years ago

Determine if today is during payroll cycle

Hi everyone, thanks in advance for help!

I have a HR app that I maintain pay data in and one of the reports I generate is our bi-weekly payroll report so that our accounting team can process and verify payroll.

Often we plan raises in advance and that is entered into our HR app but we do not want it to take place until that pay cycle.

So I am making a summary field that takes the last entry in the compensation table (most recent pay) as long as the effective date is on or before the pay date.

The challenge is how to know when my payment date is.

I have formula field calculating this coming Friday with the following code:

//Determine the Friday of this Week
LastDayOfWeek(Today()) + Days(-1)

So that should work by giving me today's date of 02/24/2023 but we are bi-weekly on payroll.  By chance today is a pay date so that works.  But if we were in next week the next pay date would be 03/10 but the formula would return 3/3.

How do I calculate that bi-weekly Friday date?



------------------------------
Ivan Weiss
------------------------------

8 Replies

Sort By
  • CurtisMiddleto1's avatar
    CurtisMiddleto1
    Qrew Cadet
    2 years ago

    Hey Ivan,

    Here is a link to the Quickbase formula functions I recommend adding to your bookmarks: https://login.quickbase.com/db/6ewwzuuj?a=td

    There is a function that perfectly addresses what you are looking for called LastDayOfPeriod().

    I haven't personally used this function, but if I'm understanding it correctly, you will put Today() as your [Date Field], Weeks(2), as your period, and then you will establish a date as your anchor for the periods moving forward. Seeing as today, 2/24/2023 is a payday, the anchor would be (2023,2,11). So your formula would look something like this: LastDayOfPeriod(Today(),Weeks(2),(2023,2,11))

    Hope this helps!



    ------------------------------
    Curtis Middleton
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      2 years ago

      I was looking at that function thinking that would be it.  However, that works this week.  How do I dynamically "shift it" every 2 weeks?  So right now the anchor is 2003,2,11.  Next Pay cycle that needs to shift to 2023,2,25

      How do I keep it moving based on where I am?



      ------------------------------
      Ivan Weiss
      ------------------------------