Calculating days between two dates where one cell is empty

[Dan012]

Hi everyone' I am trying to calculate the number of days between two dates (a start and an end date) and also where if the end date is left blank it calculates today as the end date.

This is to calculate the number of days on market for a product.

Eg. Cell A5 is the start date, cell B5 is the end date of the event I.e sold. (The end date may not be filled in as yet if the product is not yet sold). Cell C5 is the answer with total number of the products days on the market including if it still for sale e.g 54 days on market.

So I need a formula that will calculate the number of days between A&
B column if they are completed and also assume that cell B5 is today's date if it is left blank.

Any help will be greatly appreciated.

 

[jdawg_1989]

Hi,

Try

=IF(B5="",TODAY(),B5-A5)

Hope this helps

 

[Dan012]

Thanks for the reply,

When I entered the formula using these dates as a test I got the correct result when both dates were entered, I.e start date 23/6/2011 & end date 25/06/2011 it responded the correct answer (2)

On the second line same start date. I left the end date blank I returned a result of 40719 in stead of (2).

Not sure if I have made a mistake

Thanks heaps for taking the time to respond

 

[Ingolf]

Format that column as "DATE" and will show corect answer

 

[jasonb75]

Format that column as "DATE" and will show corect answer

That would show today's date, not the correct answer.

try

=IF(B5>0,TODAY(),B5)-A5

if you have a cell on the sheet that already shows today, then use that in the formula in place of the TODAY() function.

 

[Ingolf]

I answer to this:

"On the second line same start date. I left the end date blank I returned a result of 40719 in stead of (2)."

@jasonb75
your formula don't work

 

[jasonb75]

I answer to this:

On the second line same start date. I left the end date blank I returned a result of 40719 in stead of (2).

then you didn't answer it very well, 40719 formatted as date would show "25/06/2011" or similar depending on your regional format, that looks nothing like the correct answer of (2)

 

[Dan012]

thanks for everyone's efforts to help me. Unfortunately I still can't get this formula to work. Any other suggestions that may help?

 

[Scott33]

This formula entered into C5 worked perfect for me.

=IF(B5>0,B5-A5,TODAY()-A5)

 

[Dan012]

That worked great, thanks heaps for the help really appreciate it.