First day of next yearly quarter

[bondy_07]

Hello,  I have field date and I would like the function to calculate the date of first day of the next quarter.

What I mean:

1st quarter - from 01.01. to 01.03.

2nd quarter - from 01.04. to 01.06.

3d quarter - from 01.07. to 01.09.

4th quarter - fro, 01.10. to 01.12

So.... for example:

If the date is 24.05.2019 -> the calculated date must be 01.09.2019

If the date is 21.10.2019 -> the calculated date must be 01.01.2020

[RezaDorrani · Accepted solution]

Hi @bondy_07 

Expressions will be your best bet

if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),0), startofmonth(adddays(startofmonth(outputs('Compose')),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),1), startofmonth(adddays(startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),2), startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),'')))

replace outputs('Compose') - with the dynamic content of your date field

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[bondy_07]

@RezaDorrani  Thank you! It works perfectly, but could you please explain me what mean the every single command, just want to understand the code.

Thank you again!

[RezaDorrani]

@bondy_07 

if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),0), startofmonth(adddays(startofmonth(outputs('Compose')),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),1), startofmonth(adddays(startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),2), startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),'')))

The if conditions get the current month and divide it by 3 which returns 1, 2 or 0

1- Jan, Apr, July, Oct  - for this we need to go 3 months ahead

2 - Feb, May, Aug, Nov - for this we need to go 2 months ahead

3 - Mar, June, Sep, Dec - for this we need to go 1 months ahead

[bondy_07]

@RezaDorraniGreat! Thank you very much! 

[bondy_07]

@RezaDorrani 

May I ask you one more question...

I need to set date - first day of july 

For example:

If the date is to 31.06.2019 -> the date must be 01.07.2019 (the same year)

If the date is after 01.07.2019 -> the date must be 01.07.2020 (next year)

I`ve tried this code, but just want to ask you: Is it ok and is there easier way ?

if(equals(int(formatDateTime(outputs('Compose'),'MM')),1),startofmonth(adddays(startofmonth(outputs('Compose')),186)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),2),startofmonth(adddays(startofmonth(outputs('Compose')),155)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),3),startofmonth(adddays(startofmonth(outputs('Compose')),124)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),4),startofmonth(adddays(startofmonth(outputs('Compose')),93)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),5),startofmonth(adddays(startofmonth(outputs('Compose')),62)),
if(equals(int(formatDateTime(outputs('Compose')],'MM')),6),startofmonth(adddays(startofmonth(outputs('Compose')),31)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),7),startofmonth(adddays(startofmonth(outputs('Compose')),372)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),8),startofmonth(adddays(startofmonth(outputs('Compose')),341)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),9),startofmonth(adddays(startofmonth(outputs('Compose')),310)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),10),startofmonth(adddays(startofmonth(outputs('Compose')),279)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),11),startofmonth(adddays(startofmonth(outputs('Compose')),248)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),12),startofmonth(adddays(startofmonth(outputs('Compose')),186)),''))))))))))))