Hi,
I hope you are all well.
I have been trying to figure out a way to calculate month end dates based on certain conditions:
Condition1 : Quarter end dates
If the current month (i.e. the month the EM process will be run) is:
-
April (4) - monthend date should be 31/03/yyyy
-
July (7) - monthend date should be 30/06/yyyy
-
October (10) - monthend date should be 30/09/yyyy
Jan (1) - monthend date should be 31/12/yyyy
Condition 2: Half-monthly -
-
if currrent month is - July (7) - then monthend date should be 30/06/yyyy
-
if current month is - January (1) - then the monthend date should be 31/12/yyyy
This is what I have tried so far:
Option 1:
calculate a parameter called month:
monthend(today())
then use that to calculate the monthend date:
format(monthend(addmonths({month},(0-1))),‘dd/MM/yyyy’)
That gives me the date: 31/08/2021
Option 2:
calculate a parameter called ‘todaysdate’:
today()
then use that to calculate the monthend date:
format(monthend({todaysdate} -1), ‘dd/MM/yyyy’)
that gives me the date: 30/09/2021
However, that doesn’t work for what I am trying to achieve. I want the project to be fully automated so if I could do something like:
if month is - 4, 7, 10, 1 then monthend date should be 30/03/yyyy etc
Sorry for the long post and thank you in advance
Regards,
R