Skip to main content

Hello,

Using a last modified field type named "Closed" and a lookup field showing the last one called "Meter Type" and "Repeat Interval" I am trying to calculate and output the appropriate date when closed is 7/17/2024 16:41, meter type is Calendar Month, and repeat interval is 12, the formula should produce 07/31/2025. However I am repeatedly running into issues with this where 6/30/2025 or 7/1/2025 is all I can produce. 

Here is the formula I am currently using:

 

 

IF(
{Meter Type} = "Calendar Month",
IF(
{Due Date},
DATEADD(
DATEADD({Due Date}, {Repeat Interval}, 'months'),
-DAY(DATEADD({Due Date}, {Repeat Interval}, 'months')) + 1,
'days'
),
DATEADD(
DATEADD({Closed}, {Repeat Interval}, 'months'),
-DAY(DATEADD({Closed}, {Repeat Interval}, 'months')) + 1,
'days'
)
),
BLANK()
)

 

However the output is 7/1/2024 11:41pm when it should be 07/31/2024.

Thanks

I've created a simplified working version here for you that just calculates the last day of the current month, one year from now that you can add to your formula to make it work

DATEADD(
DATEADD(
DATETIME_PARSE(
DATETIME_FORMAT(
Date,
'MM YYYY'
),
'MM YYYY'
),
13,
'months'
),
-1,
'days'
)

 

Hi,
I would create it in a following way. It works OK, when I tested, but I just realized I'm always using ISO, there is a small chance it fail for other format.

 

IF({Meter Type}='Calendar Month',
DATEADD(REPLACE(
DATEADD(IF({Due Date},{Due Date},Closed),
1+{Repeat Interval},'month')
,9,2,'01'),
-1,'day'))

 

Note: if you have several Meter Types, instead of create a tree, repeating formula above for each Meter Type,
just replace 'month' with SWITCH for each type,

 

SWITCH({Meter Type},
'Calendar Month' , 'month',
'Something else' , 'week' )

 

or something like that

 

LOWER(Substitute({Meter Type}, 'Calendar ' , ""))

 



Thank you!

Reply


Terms & Conditions