Minus two values but enter text if one cell is blank

[Steve1977]

Been wracking my brains with this one and I suspect there is an easy solution but I cannot figure it out.

I'm trying to work out the number of days since a particular item has sold but in some instances, it has never sold and so Excel churns out a reference of 43726.

REF	LAST SOLD	CURRENT DATE	DESIRED VALUE
REF1	15/09/19	18/09/19	3
REF2	01/09/19	18/09/19	18
REF3	<null></null>	18/09/19	Never Sold

<tbody>
</tbody>

Is there a way to achieve my desired values above? I suspect a nested IF formula? Having a real hard time

 

[Special-K99]

IF(H2="","Never Sold", < put the above formula here > )

 

[LlebKcir]

a blank date field will result in 01/01/1900 so yes 119 years would be the correct calculation.

 

[Steve1977]

=IF(H3="","Never Sold",IF(H3>0,J3-H3,"Never Sold"&DATEDIF(H3,J3,"y")&" years, "&DATEDIF(H3,J3,"ym")&" months, "&DATEDIF(H3,J3,"md")&" days"))


I applied it like the above, but now it is ignoring the last bit where it puts it into Years, Months and Days.

The way I understand it is that the code in red looks for nothing and if so, it puts in 'never sold' but then the code in Blue does the same job?

Little aside, I can get this working within 2 Columns but doing a simple H3-J3 in one column and then the DATEDIF in another. But ideally I'll be able to sort it out in one column and boost my knowledge at the same time.

 

[Special-K99]

Not sure what youre asking for.
You said "if H2 is blank"
So I just added an IF H2 is blank around the original formula.

If H2 is not > 0 then it will produce "Never Sold" plus the dates.
Since you're only checking for H2 > 0 to produce J2-H2 then a blank would automatically produce "never Sold" & the dates,
by blank I mean no entry in the cell, a null, "", not a space

 

[Steve1977]

I'm getting into a bit of tizz with this aren't I, kinda losing track lol

But basically, if H2 is blank then it needs to state 'Never Sold'

But if there is a value then it does J2-H2 and then puts the answer formatted in Years, Months and Days.

This formula shows the answer in numeric value - =IF(H2>0,J2-H2,"Never Sold")

But I want to nest this in the same cell so if there is a number, it applies it in Years, Months and Days -
DATEDIF(H5,J5,"y")&" years, "&DATEDIF(H5,J5,"ym")&" months, "&DATEDIF(H5,J5,"md")&" days")


Little aside, I can get this working within 2 Columns but doing a simple H3-J3 in one column and then the DATEDIF in another. But ideally I'll be able to sort it out in one column and boost my knowledge at the same time.

 

[Steve1977]

Apologies if this is getting a little confusing, but ultimately this is how they all work so far:

Attempt 01
States 'Never Sold' if H2 is blank - If not blank, it J2-H2 as numeric
=IF(H2>0,J2-H2,"Never Sold")


Attempt 02
States 'Never Sold, 118 Years, 8 Months, 19 Days' if H2 is Blank. If not blank, it displays J2-H2 as numeric (so it works backwards to how I want it)
=IF(H2>0,J2-H2,"Never Sold"&DATEDIF(H2,J2,"y")&" years, "&DATEDIF(H2,J2,"ym")&" months, "&DATEDIF(H2,J2,"md")&" days")


Attempt 03
States 'Never Sold if H2 is blank (correct). If not blank it displays J2-H2 as numeric and not in date format. So it works as Attempt 01 and ignores the DATEDIF
=IF(H2="","Never Sold",IF(H2>0,J2-H2,"Never Sold"&DATEDIF(H2,J2,"y")&" years, "&DATEDIF(H2,J2,"ym")&" months, "&DATEDIF(H2,J2,"md")&" days"))

 

[LlebKcir]

how about another IF for attempt 02. =IF(H2="","Date_You_Started_Tracking",=IF(H2>0,J2-H2,"Never Sold"&DATEDIF(H2,J2,"y")&" years, "&DATEDIF(H2,J2,"ym")&" months, "&DATEDIF(H2,J2,"md")&" days")))


Again the 118 or 119 years is due to a blank date field setting to 1/1/1900 or Jan 1, 1900. So yes the answer is correct. If you manually populate a date in that field you will not receive the Never Sold, but some date.

You could also reverse the process such that at the end you do a check and if the Y > 50 set the output the Never Sold. change year to match how ever old your tracking system goes back.

 

[Steve1977]

Thank you LlebKcir, I tried the modification to Attempt 02 but it then ignored 'Never Sold' and just put in the date I put in 'Date_You_Started_Tracking and ignored 'Never Sold'

From what I can gather then, what I'm asking is not possible due to Excel putting Blank date field setting to 1/1/1900? The thing is, in Attempt 3 it will show 'Never Sold' for zero values but then ignore the DATEIF. If I could somehow get it to acknolwedge the DATEIF, it would work - argh!!!

 

[Steve1977]

Managed to suss it!

=IFERROR(DATEDIF(IF(B2=0," ",B2),D2,"y")&" years, "&DATEDIF(B2,D2,"ym")&" months, "&DATEDIF(B2,D2,"md")&" days","Never Sold")

 

[LlebKcir]

cheers, glad you worked it out.