Results 1 to 4 of 4

Thread: Generating a DATEDIF value of "0" when the EndDate is in the past and then using in IF formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Generating a DATEDIF value of "0" when the EndDate is in the past and then using in IF formula

    Hello!

    I am trying to use a DATEDIF formula to calculate a number that is then used to in an IF formula to generate a score. The issues is that for some of the DATEDIF calculations the EndDate is before the StartDate. When this happens, I have the formula generate a "0". I then use the outcome of the DATEDIF for an IF formula but when the outcome is "O" the IF formula doesn't work properly.

    (DATEDIF Formula) =IF(AI5="","NO ONBOARDING DATA",(IFERROR(DATEDIF(T5,AI5,"d"),"0")))

    (IF Formula) =IFERROR(IF(AJ5="","0.00",IF(AJ5<=7,"0.05",IF(AJ5<=14,"0.04",IF(AJ5<=21,"0.03",IF(AJ5<=365,"0.01",IF(AJ5>365,"0.00")))))),"0")

    Why won't the "0" be considered in the IF(AJ5<=7,"0.05"??? How can I fix this so that all situations where the EndDate is in the past, the IF formula results in "0.05"?

    Thank you!

  2. #2
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Generating a DATEDIF value of "0" when the EndDate is in the past and then using in IF formula

    in the IFERROR () you have "0" - thats a text zero
    change to just 0
    (DATEDIF Formula) =IF(AI5="","NO ONBOARDING DATA",(IFERROR(DATEDIF(T5,AI5,"d"),0)))
    Now the zero is a number and NOT text
    again here
    =IFERROR(IF(AJ5="","0.00",IF(AJ5<=7,"0.05",IF(AJ5<=14,"0.04",IF(AJ5<=21,"0.03",IF(AJ5<=365,"0.01",IF(AJ5>365,"0.00")))))),"0")
    you have "" inverted commas around a number - remove them ,
    Wayne

    Using Excel Version 365 for Mac

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Generating a DATEDIF value of "0" when the EndDate is in the past and then using in IF formula

    Thanks Etaf! What a simple fix Formula works now and I will be more careful with my "0"s...

  4. #4
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Generating a DATEDIF value of "0" when the EndDate is in the past and then using in IF formula

    you are welcome
    Wayne

    Using Excel Version 365 for Mac

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •