2

I buy and sell different items. I am trying to write a formula that will work out how long an item stayed in stock. For example:

A1 is Purchase Date, so I enter 1st June 2018
A2 is Sale Date, 
A3 is No. of Days in Stock 

Now if I use:

A2-A1 -- This will give me the no of days only if there is a value in A2. If there is no value in A2, I will not get an output.

If I use

=datedif(a1,today(),"d")

I will get a value in A3 but this is not correct.

I am looking for a formula that will give an output in A3 based on how many days it took me to sell an item.

If there is no Sale Date in A2, it will continue to give me the number of days since Purchase Date (A1).

If there is a Sale Date in A2, it will work out the days from Purchase to Sale but not continue to add any more days.

1
  • How about: =IF(OR(A1="",A2=""),"",A2-A1) Jul 11 '18 at 18:43
4

Use the ISBLANK worksheet function.

=IF(ISBLANK(A2),DATEDIF(A1,TODAY(),"d"),DATEDIF(A1,A2,"d"))

The above code will show you the dates the item has remained in stock from the date of purchase to the date of sale and also if the item has not sold it will show how long it's been available for sale.

1
  • 1
    This works perfectly. Thank you for your help.
    – ALI
    Jul 12 '18 at 16:52
1

You deal with a conditional problem, where A2 is either empty (not sold yet) or has a date (sales date).

You can use the IF() function, which takes three arguments:

  • a condition
  • a value to return if the condition is true
  • a value to return if the condition is false

As the condition you can use the ISBLANK() function.

ISBLANK(A2)

returns TRUE if the A2 is blank and FALSE if it is not.

Thus, the formula to enter in A3 becomes

=IF( ISBLANK(A2) , TODAY()-A1 , A2-A1 )

The returned value in A3 is the number of days between either purchase and todays dates, or purchase and sales dates.

2
  • 2
    Do you mean ISBLANK() function? because ISEMPTY is a VBA function.
    – angelofdev
    Jul 12 '18 at 0:07
  • Yes, @angelofdev Thanks for the heads up! What a silly mistake I made. I have to translate from finnish, and the function name in finnish is ONTYHJÄ, which litterally means is empty. I should have double checked. Jul 12 '18 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.