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

1. 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!   Reply With Quote

2. 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 ,  Reply With Quote

3. 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...  Reply With Quote

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

you are welcome  Reply With Quote

User Tag List

Tags for this Thread

datedif, datedif formula, datedif function, formula, if formula  Posting Permissions

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