# Re: Getting error while using IF & MIN statement together

Solved
Jump to Solution
422 0
cancel
Showing results for
Search instead for
Did you mean:
6 - Interface Innovator

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.

1 Solution

Accepted Solutions
10 - Mercury

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.

2 Replies 2
10 - Mercury

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.

6 - Interface Innovator

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!