Problem in my Nested IF-THEN-ELSE statement

[Vaibhav_Mohunta]

Hi,

I am trying to write a Nested conditional statement for the following:

I have one column named Contract End Date. In one of the other columns, I need text which would say comparing to Today, if number of days before the contract expires is More than 45 Days or More than 90 days
Following is my attempt to code:

IF((DATETIME_DIFF({Contract End Date}, TODAY(), ‘days’) >=45),“Greater than 45”, IF(DATETIME_DIFF({Contract End Date}, TODAY(), ‘days’)>90,”More than 90 days”,””))

Trying to put this condition but I have shown an error saying “Sorry, there was a problem saving this field. Invalid formula. Please check formula text.” I can’t find where the error is.

Urgently need help! :disappointed_relieved:

[W_Vann_Hall]

@Vaibhav_Mohunta

I suspect you copy/pasted that formula snippet from a post elsewhere in this forum. :winking_face: It’s a problem that pops up now and again…

If so, change the ‘pretty-printed’ (aka ‘inverted comma’) single and double quotes to keyboard-standard ones. (That is, instead of ‘ or ’ use ’ ; instead of ” or ” use " .)

If that’s not the case, report back and I’ll whack it again…

[Vaibhav_Mohunta]

W_Vann_Hall:

’

Hey @W_Vann_Hall

Thank you for your reply! I tried your suggestion but it still doesn’t work :worried:

IF(DATETIME_DIFF({Contract End Date}, TODAY(), ’days’) >=45,“Greater than 45”, IF(DATETIME_DIFF({Contract End Date}, TODAY(), ’days’)>90,“More than 90 days”,""))

Have I correctly used the different commas here ? I can’t think of any reason why this code shouldn’t work now

[Vaibhav_Mohunta]

The inverted commas here might seem different in the text I sent, but I used the one you suggested in the code

[Vaibhav_Mohunta]

Actually, the code I sent was a simplified one from my actual one which I am wrote by myself. However, the code did not run. The 4 conditions that I need is:
If contact end date - Today’s Date < 1, display days
If contact end date - Today’s Date < 30, display days
If contact end date - Today’s Date < 45, display "Less than 45 days"
If contact end date - Today’s Date > 90, display "More than 90 days"
Else, display “More than 45 days but less than 90 days”

Following is the code:

IF(DATETIME_DIFF({Contract End Date}, TODAY(),’days ’)<1, DATETIME_DIFF({Contract End Date}, TODAY(), ’days ’)& " days ",IF(DATETIME_DIFF({Contract End Date}, TODAY(), ’days ’)<30,TONOW({Contract End Date}),IF(DATETIME_DIFF({Contract End Date}, TODAY(), ’days ’)<45, "Less than 45 Days ",IF(DATETIME_DIFF({Contract End Date}, TODAY(), ’days ’)>90, "More than 90 Days ", "More than 45 Days Less than 90 Days "))))

I would really appreciate if I can get some help on it :slightly_smiling_face:

[W_Vann_Hall]

Oh, got it: Change 'days ’ to ‘day’ or ‘days’. For some reason, that trailing space ends up giving you results in the billions of days.

Extraneous space in DATETIME_DIFF units parameter greatly skews results Formulas

OK, so, strictly speaking not a bug, but certainly an infelicity. :slight_smile: Maybe someone can step on it next time something else needs fixing in DATETIME_DIFF… For background, see this post in the Support forum. Specifying 'day ' or 'days ' rather than 'day' or 'days' produces wildly incorrect results: [datetime_diff-bug] Thanks!