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
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 ?