Skip to main content
Solved

Getting error while using IF & MIN statement together

  • June 13, 2023
  • 2 replies
  • 31 views
M
Forum|alt.badge.img+6

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.

Best answer by Stephen_Orr1

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

S
Forum|alt.badge.img+18
  • Stephen_Orr1
  • Inspiring
  • 272 replies
  • Answer
  • June 13, 2023

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.

M
Forum|alt.badge.img+6
  • Mayank_Agarwal
  • Author
  • Known Participant
  • 10 replies
  • June 13, 2023

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