Skip to main content

Problem in my Nested IF-THEN-ELSE statement

  • October 14, 2017
  • 5 replies
  • 57 views
V

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:

    5 replies

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • October 14, 2017

    @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…

    V
    • Vaibhav_Mohunta
    • Author
    • New Participant
    • October 14, 2017

    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

    V
    • Vaibhav_Mohunta
    • Author
    • New Participant
    • October 14, 2017

    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


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

    V
    • Vaibhav_Mohunta
    • Author
    • New Participant
    • October 14, 2017

    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
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • October 14, 2017

    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:


    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.

    Terms & ConditionsAccessibility statement