Find the first date of the current Qtr and Last date of the next Qtr

[lomato]

Hi Everyone,

I have a field named DueDate, containing dates in this format YYYY-MM-DD 12:00:00 AM

I want to create following two variables:

• vFirstDate = should be the first day of the current Qtr
• vLastDate = Should be the last day of the next Qtr

As an example: Today is 12 August 2020

• vFirstDate should be  2020-07-01 12:00:00 AM 
• vLastDate should be 2020-12-31 12:00:00 AM

What expression should I use in this variables to find the above desired value?

[rushikale0106 · Accepted solution]

Hello @lomato ,

I hope I have a solution to your query here. I tried the example with Today = 13th August 2020.

We have Date and Time functions to find the first date and the last date of the Quarter. Considering this I used QuarterStart() and QuarterEnd() function. 

The Variable script will be on Script Editor and it will be as follows:

LET DueDate=Today();
LET vFirstDate = QuarterStart(DueDate);
LET vLastDate = QuarterEnd(AddMonths(DueDate, 3))

(To find the end date of the next quarter I added 3 months with the current date. Also, the Date format can be changed from the script editor. It won't be any issue)

Hope it will help you with your query. Let me know if you face any other issue.

Regards,

Rushika

[lomato]

Thanks @rushikale0106 ,

It seem to be working but now I have a similar followup question.. how to get two variable as below

vCurrentQtr = should show the current Qtr number and current year

vNextQtr = shuld show next Qtr number and year as

So as an example, If today is 13 Aug 2020 then variable value for

• vCurrentQtr should be Q3-2020
• vNextQtr should be Q4-2020

[rushikale0106]

Hi @lomato ,

The solution to your latest query can be solved using Date and time function only.

The script will be written on the Script Editor window.

LET vCurrentQtr1 = QuarterName(DueDate);
LET vCurrentQtr2 = 'Q' & Ceil(Month(DueDate)/3) & ' - ' & Year(DueDate);
LET vNextQtr = 'Q' & Ceil(Month(AddMonths(DueDate, 3))/3) & ' - ' & Year(DueDate);

Follow whichever way you want to display. 

Hope it will solve your query and if it does, mention the answer as a solution. It will help others to understand.

Regards,

Rushika

[lomato]

Thanks, both solutions worked as expected. Thank you for your help 🙂

[rushikale0106]

Welcome!

Glad to help!

[lomato]

bytheway.. instead of using DueDate.. if I use today's date to find current Qtr-Year and Next Qtr-Year.. would this formula work?

If I use following formula to find the next Qtr based on today's date.. how will it now the year for next Qtr?

= 'Q' & Ceil(Month(AddMonths(now(), 3))/3) & '-' & Year(now())

Lets say today is 15 December 2020 then will the above formula give me the right year?