Minus two values but enter text if one cell is blank

Steve1977

New Member
Joined
May 16, 2019
Messages
33
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.


REFLAST SOLDCURRENT DATEDESIRED VALUE
REF115/09/1918/09/193
REF201/09/1918/09/1918
REF3<null></null>18/09/19Never Sold

<tbody>
</tbody>


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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Assuming data in columns A, B, C, then desired value formula for cell D2 could look like this:
Code:
=IF(B2>0,C2-B2,"Never Sold")
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
@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 < >
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
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

Rich (BB code):
=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.

Rich (BB code):
=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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top