Forum Discussion

SandraleeOster's avatar
SandraleeOster
Qrew Trainee
4 years ago

Calculate Next Due Date based on End of Quarter

How do I calculate a next quarter due date based on last day of quarter?

Quarter End Dates:
03/31
06/30
09/30
12/31

Thank you.

------------------------------
Sandralee Oster
------------------------------
  • To clarify what I am trying to do.

    I have a previous due date and I have a next quarter due date.  What I need to do is if the Previous due date is 03/31/2021 then the next quarter due date needs to be 06/30/2021.  This formula works up until I put a future date like 01/01/2022 and then it returns blank.

    //Calculate the quarter end dates

    If(
    [Previous Quarter Date]<(ToDate("3/31/" & (Year(Today())))),ToDate("3/31/" & Year(Today())),
    [Previous Quarter Date]<(ToDate("6/30/" & (Year(Today())))),ToDate("6/30/" & Year(Today())),
    [Previous Quarter Date]<(ToDate("9/30/" & (Year(Today())))),ToDate("9/30/" & Year(Today())),
    [Previous Quarter Date]<(ToDate("12/31/" & (Year(Today())))),ToDate("12/31/" & Year(Today()))

    ------------------------------
    Sandralee Oster
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      try this

      LastDayOfMonth(AdjustMonth([Previous due date],3))

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
      • SandraleeOster's avatar
        SandraleeOster
        Qrew Trainee
        Thank you!  That worked.

        ------------------------------
        Sandralee Oster
        ------------------------------