Skip to main content

Hello!

I am trying to create a formula which gives an output of difference between two dates (Premium date & Last month from Today date - as seen in the last 2 columns). The base is primarily used to manage my Insurance payments, so keeping that context in mind if its helpful.

 

The issue is - I have two Premium dates in my table (because some times Premium might be half-yearly frequency payment).

 

So, in order to get the difference in dates for only 1 date (Yearly premium) - I used the following formula:

IF({Plan status}="Active", DATETIME_DIFF(DATETIME_FORMAT({Premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS'),'NA')

And this is working fine.

But, when I introduce 2 dates into the formula - I also need to use a MIN() function that gives me the lower of the difference values.

Here is the formula I tried using for it, but it returns NaN -

IF({Plan status}="Active",MIN( DATETIME_DIFF(DATETIME_FORMAT({Premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS'),DATETIME_DIFF({Next premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS'))

I'm not sure why I'm getting this error. Just to be certain I have checked commas and brackets in the MIN() formula as well - but I'm not able to detect the error.

Please help.

You're missing a 'DATETIME_FORMAT' in your second code block here (and a final parenthesis to everything once this is added)

DATETIME_DIFF({Next premium pmt date},'DD-MMM')

Try this

IF({Plan status}="Active", MIN( DATETIME_DIFF(DATETIME_FORMAT({Premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS') , DATETIME_DIFF(DATETIME_FORMAT({Next premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS')) )

 I'm not sure datetime_format is truly necessary if your fields are producing dates. Haven't tested though.

You're missing a 'DATETIME_FORMAT' in your second code block here (and a final parenthesis to everything once this is added)

DATETIME_DIFF({Next premium pmt date},'DD-MMM')

Try this

IF({Plan status}="Active", MIN( DATETIME_DIFF(DATETIME_FORMAT({Premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS') , DATETIME_DIFF(DATETIME_FORMAT({Next premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS')) )

 I'm not sure datetime_format is truly necessary if your fields are producing dates. Haven't tested though.


Hey Stephen! Thanks so much for the solution! 

The reason I'm adding the datetime_format is because I want to omit the year in the dates and only consider DD-MMM.

Thank you so much!

Terms & ConditionsAccessibility statement