Calculate quarterDeadline from paymentDate

[Nadege_BOINNARD]

I have a little brain exercise and I’m running in a hamster wheel and cannot see to find the logic to use or if there’s a function out there that would solve my problem. I’ll take any help I can get :)))

How can I create a formula doing the following behavior ?

Variable : paymentDate - formatted in YYYY-MM-DD (ISO)

I want to make each line appear if it fits the criteria.
A paymentDate can belong to Quarter 1, 2, 3 or 4.
Q1 is from Jan 1st to March 30th
Q2 is from Avril 1st to June 30th
etc…

We have one month to prepare our reports after a quarter ends. What I want is to hide all the records from a current quarter until the deadline, i.e. all the payments made during Q1 2022 for instance will not be visible by my partners before April 30th (March 30th + 1 month).

What I need is to be able to calculate a date (quarterDeadline, for instance) that will be April 30th, July 31st, October 31st, and then January 31st (of the next year). But this date of course needs to carry the year… because then my filter will very simple : “quarterDeadline” < TODAY() (or NOW(), I don’t remember) and it’s done.

The real problem is to define the formula that is going to calculate quarterDeadline from paymentDate.

Could you guys help ?

[Kamille_Parks]

This can be done with the following formula:

IF(
   {paymentDate}, 
   DATEADD(
      DATEADD(
         DATETIME_PARSE(
            DATETIME_FORMAT({paymentDate}, "YYYY-Q"), 
         "YYYY-Q"), 
      4, "month"), 
   -1, "day")
)

Explanation, working from the middle outwards:

• Format the {paymentDate} into the year and quarter. This would turn “2022-02-04” into “2022-1”
• Parse ^ that out into a date. Now we go from “2022-1” to “2022-01-01” (the first day of the quarter)
• Add 4 months to that^ day. Now we’re at “2022-05-01”. You want to add 4 months because quarters are each 3 months and your deadline is one month beyond the end of the quarter.
• Subtract 1 day from that^. Now we’re at “2022-04-30”. This is the last day of the first month after the quarter {paymentDate} falls in.
• Use an IF() clause to only perform the above calculations if there is a {paymentDate} to begin with. Otherwise you’d get an error on all records where that field is blank.

Now you can use filters in views set to when {quarterDeadline} is before today.