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.


[TABLE="width: 500"]
<tbody>[TR]
[TD]REF[/TD]
[TD]LAST SOLD[/TD]
[TD]CURRENT DATE[/TD]
[TD]DESIRED VALUE[/TD]
[/TR]
[TR]
[TD]REF1[/TD]
[TD]15/09/19[/TD]
[TD]18/09/19[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]REF2[/TD]
[TD]01/09/19[/TD]
[TD]18/09/19[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]REF3[/TD]
[TD]<null></null>[/TD]
[TD]18/09/19[/TD]
[TD]Never Sold[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Joe4]

Assuming data in columns A, B, C, then desired value formula for cell D2 could look like this:

=IF(B2>0,C2-B2,"Never Sold")

 

[Steve1977]

Thank you ever so much for this!

In another column I also had the following formula =DATEDIF(0,K2,"y")&" years " &DATEDIF(0,K2,"ym")&" months "&DATEDIF(0,K2,"md")&" days"
This translated the last sold vs current date into a more readable way.

However now some rows say 'Never Sold', it comes up with an error in those instances. How would I add something to that formula to say 'never sold' or to remove the N/A's?

 

[Joe4]

Do the exact same thing we did in the last one, wrap your formula in na IF statement, checking to see if the value in column D is greater than zero.
It should pretty straightforward. Give it a shot, see how you do! It will be a good test to see if you understand what I did in the previous response.

 

[Steve1977]

Cheers mate

I have been trying to do this and also understand the fundamentals of Nesting IF formulas and I feel I have cracked it, but for some reason the formula states "too many arguments". This is my understanding of it: (I've used different colours to assist me in identifying the individual components.

If H2 is greater than zero then do the Formula of J2-H2, else, show 'Never Sold'
=IF(H2>0,J2-H2,"Never Sold")


To Nest IF Formulas a comma is used like above and also in the examples below:
i.e. if B3 and C3 are same value, put 'flight full'. If B3 less than C3 then put 'overbook' else 'space available'
=IF(B3=C3,"FlightFull",IF(B3<C3,"overbooked","spaceavailable"))


So with this knowledge I figured if I did the following, it would show me the value in Years, Months and Days and 'Never Sold' if it's blank:
=IF(H2>0,J2-H2,"Never Sold",DATEDIF(H2,J2, "y") &" years, "&DATEDIF(H2, J2, "ym") &" months, " &DATEDIF(H2, J2,"md") &" days" - error message "too many arguments"


So I figured, lets put a & next to the first DATEDIF like the other ones are, but then it doesn't recognise it as a formula
=IF(H2>0,J2-H2,"Never Sold",&DATEDIF(H2, J2, "y") &" years, "&DATEDIF(H2, J2, "ym") &" months, " &DATEDIF(H2, J2, "md") &" days"


Any assistance in getting over the final hurdle would be much appreciated.

 

[Special-K99]

@Steve1977: This forum gets confused with < > characters, thinks it's HTML related and so doesn't display them so your post looks odd. To correct this place a space before and after each < >

 

[Peter_SSs]

This forum gets confused with < > characters, thinks it's HTML related and so doesn't display them so your post looks odd. To correct this place a space before and after each < >

FYI: The problem is only with < characters and only if followed by a letter and you only need a space after those ones, not before.

 

[Steve1977]

Apologies, here is my post with that (hopefully rectified)

I have been trying to do this and also understand the fundamentals of Nesting IF formulas and I feel I have cracked it, but for some reason the formula states "too many arguments". This is my understanding of it: (I've used different colours to assist me in identifying the individual components.

If H2 is greater than zero then do the Formula of J2-H2, else, show 'Never Sold'
=IF(H2>0,J2-H2,"Never Sold")


To Nest IF Formulas a comma is used like above and also in the examples below:
i.e. if B3 and C3 are same value, put 'flight full'. If B3 less than C3 then put 'overbook' else 'space available'

(space inserted after < due to Forum issues)
=IF(B3=C3,"Flight Full",IF(B3< C3,"overbooked","space available"))



So with this knowledge I figured if I did the following, it would show me the value in Years, Months and Days and 'Never Sold' if it's blank:
=IF(H2>0,J2-H2,"Never Sold",DATEDIF(H2,J2, "y") &" years, "&DATEDIF(H2, J2, "ym") &" months, " &DATEDIF(H2, J2,"md") &" days" - error message "too many arguments"


So I figured, lets put a & next to the first DATEDIF like the other ones are, but then it doesn't recognise it as a formula
=IF(H2>0,J2-H2,"Never Sold",&DATEDIF(H2, J2, "y") &" years, "&DATEDIF(H2, J2, "ym") &" months, " &DATEDIF(H2, J2, "md") &" days"


Any assistance in getting over the final hurdle would be much appreciated.

 

[LlebKcir]

Steve1977 the IF statement can be read as follows:

IF --- Question --- True_answer --- False_answer

of IF this, Than That

https://support.office.com/en-us/article/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

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

reads: IF H2 greater than 0, than J2 - H2, else: "Never Sold", --- error, cannot the last comma as that start the next argument. Move that comma inside the quotes if you need it to be displayed, else just user the & symbol.

https://support.office.com/en-us/article/DATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c

Interesting function. new one to me.

=IF(H2>0, J2-H2, "NEVER SOLD" & DATEDIF(H2, J2, "Y") & "YEARS, " & DATEDIF(H2, J2, "YM") & "MONTHS, " & DATEDIF(H2, J2, &"DAYS"))

might do the trick. i have not tested that.

 

[Steve1977]

Thank you

I thought the comma was used at the beginning of a new IF statement so thank you for clarifying.

I have implemented the formula as

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

Then I subsequently looked at yours to confirm I'm on the right path so thank you for posting that also.

Interestingly though, when H2 is blank it states "Never Sold 119 years, 8 Months, 19 Days"

So it appears it's still doing the whole of the formula - which is understandable really because that's what the formula states.
But is there a way either by Formula or VBA code to ignore the rest of it if H2 is blank and so it will simply read 'Never Sold' as opposed to the above.