Comparing DATEDIF() results - return values for results

[nobeja]

Is it possible for this formula

=IF(DATEDIF(A1;B1;"y")=0;"";DATEDIF(A1;B1;"y")&" years ")&IF(DATEDIF(A1;B1;"ym")=0;""; DATEDIF(A1;B1;"ym")&" months ")&DATEDIF(A1;B1;"md")&" days"

that display result in this format

3 years 11 months 0 days

to create formula with IF function or some other function
to return value for results:

if less than 6 years 0 months 0 days value is 0

if between 6 years 0 months 0 days and 20 years 0 months 0 days value is 1

if more than 20 years 0 months 0 days value is 2

I have try some examples with IF and LOOKUP function, but I couldn’t setup formula to work correctly.

[Lupp]

May I suggest you edit the subject to emphasize properly that the question is about comparing DATEDIF() results?

[mariosv]

I think something like =IFS(DATEDIF(A1;A2;"y")<6;0;DATEDIF(A1;A2;"y")>=20;2;1;1) does the job

[nobeja]

Thank you for your answer.

With formula




=IFS(DATEDIF(A1;A2;“y”)<6;0;DATEDIF(A1;A2;“y”)>=20;2;1;1)




I also getting same results like with formula




=TEXT(DATEDIF(A4;B4;“y”);“0000”)&" years “&TEXT(DATEDIF(A4;B4;“ym”);“00”)&” months “&TEXT(DATEDIF(A4;B4;“md”);“00”)&” days"




except In case when there is need


for result 20y 00m 00d to be value 1


and for result 20y 00m 01d to be value 2.



If instead of years I add days



=IFS(DATEDIF(I7;J7;“d”)<2191;0;DATEDIF(I7;J7;“d”)>7305;2;1;1)





I get result for 20y 00m 00d to be value 1

and for 20y 00m 01d to be value 2.

But since there are not same number of days in, for example, time range from 1950 to 2000 and from 2000 to 2050, I don’t know if changing years to days in formula should be right thing to do when it comes to accuracy.

[Lupp]

Use a strictly formatting formula like

=TEXT(DATEDIF(A4;B4;"y");"0000")&" years "&TEXT(DATEDIF(A4;B4;"ym");"00")&" months "&TEXT(DATEDIF(A4;B4;"md");"00")&" days"  

for all the values needing to be compared, and you can directly compare date differences by any ordinary comparator (comparing operator) like =, <>, <=, and so on.

The crux of comparing somehow numeric values expressed as texts, is mostly the omition of leading zeroes. Of course, in addition you need a “Bigendian” format.

[nobeja]

Thank you for your answer.

Example about this solution:

=TEXT(DATEDIF(A4;B4;"y");"0000")&" years "&TEXT(DATEDIF(A4;B4;"ym");"00")&" months "&TEXT(DATEDIF(A4;B4;"md");"00")&" days"

*I will change cells numbers since I have data in other columns and rows, but formula is the same.*

In cell **I6** is **start date**
in cell **J6** is **end date**
in cell **K6** is formula:

=TEXT(DATEDIF(I6;J6;"y");"0000")&" years "&TEXT(DATEDIF(I6;J6;"ym");"00")&" months "&TEXT(DATEDIF(I6;J6;"md");"00")&" days"
displayed in this format **0020 years 00 months 01 days**

in cell **R6** is formula:

=IF(K6<"0006 years 00 months 00 days ";"0";IF(AND(K6>="0006 years 00 months 00 days ";K6<="0020 years 00 months 00 days ");"1";"2"))

example for formula in cell **R6** was found [here](https://stackoverflow.com/questions/18708561/if-function-with-3-conditions)

[Lupp]

My answer exlusively addresses the case that you have some (lots of) date differences expressed as single textual results in years, months, and days - and that you want to compare them in conditional formulas. If so, you have more than one problems:
Ready to write a complicated comparison, you have trouble with extracting the single numbers from the triple.
Wanting to get a direct comparison you have the problem that the lexicographical comparison often fails due to different lengths of the numeric strings.
Doing as I suggested you needn’t worry about the first problem because you dont experience the second one at all.
However my solution depends on having expressed all date differences in the suggested - or one other equally benevolent format.
Generally the DATEDIF() stuff should be avoided wherever possible for lots of disadvantages.

[nobeja]

Solution that you suggested works for me since I need to compare list of data that contains start date and end date, and based on time range between dates to add value 0, 1 or 2.

I wrote example about your solution in case someone else needed, all information to be in one place (solution that you suggested and IF function with 3 conditions (formula that is in cell R6).

[mikekaganski]

If I understand the question correctly, OP wants to adjust an existing formula that displays the difference as text, to use in conditions instead. But imo, simply doing IF(A4-B4<365*6;0;IF(A4-B4<=365*20;1;2)) is better. It doesn’t account for leap years, true.

Edit 2019-06-24 09:33+1000

Another variant:

=IF(DATEDIF(A4;B4;"y")<6;0;IF(DATEDIF(A4;B4;"y")<20;1;2))

or

=VLOOKUP(DATEDIF(A4;B4;"y");{0;0|6;1|20;2};2)

[nobeja]

Thank you for your answer.

I try formula but for some reason I don’t get results that I suppose to.

For example:

with date range that give result 19y 11m 25d, I get value 1


and with date range where result is 19y 11m 26d, I get value 2



With condition for 6 years (J8-I8<=365*6;0;) everithing is ok.

I also try this formula,but noting changes.

IF(J8-I8<=365*6;0;IF(J8-I8>=365*20;2;1))

(in my case J8 is end date and I8 is start date)

Should I maybe format cells differently or to do some other settings so that formula should work correctly?

[mikekaganski]

As I wrote, the formula doesn’t take leap years into account.

See edit of my answer for another variant.