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.

 

[jasonb75]

Typo, should have been

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

 

[Scott33]

Typo, should have been

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

Actually, we are saying the same thing, your just subtracting outside the IF statement and I'm subtracting inside the IF statement. No TYPO.

Cheers!

 

[jasonb75]

Actually, we are saying the same thing, your just subtracting outside the IF statement and I'm subtracting inside the IF statement. No TYPO.

Cheers!

Sorry Scott, I wasn't criticizing your formula, I meant I had a typo in my earlier post, =IF(B5>0,TODAY(),B5)-A5

 

[Dan012]

Ok to make this problem a little trickier again, if I copy the formula down the column how do I get the cells to not show today's date where there is no start date and return a zero value.

The reason I need this is so I can add an average calculation at the bottom of the column and with the empty cells returning today's date I can not get a true average number?

Once again any help is greatly appreciated. Thanks

 

[jasonb75]

Try

=IF(A5,IF(B5,B5,today()))-A5

 

[Dan012]

Sorry Jason still returned today's date as the answer when both date fields were left blank.

 

[Dan012]

Went back and tried again I must have made a typo. Thanks heaps for the help.

 

[jasonb75]

Try it again, I edited the formula, sounds like you copied it before I saved.

 

[thomasj76]

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

Guys, I tried all the above for my need but it partially works. Let me explain my need and result. I am trying to calculate TAT for closure of problems reported.

My Cell G has start date, Cell H has end date but in some cases, Cell H will be blank since issue is not resolved yet. If I use the formula =IF(((H2-G2))<0,TODAY()-G2) I get correct number of days for the ones that are blank but if there is a date in Cell H, my result returns as "False".

Can someone help?

 

[huper]

Hi,

I'm trying to create a formula to calculate billable days in a date range for storage of items.
I've gotten as far as H3 (Billable Days) =IF(F3>0,F3-$I$1+1,$K$1-$I$1+1)
The only issue with the above formula is if E3 is > than I1, I don't get the correct result.

Invoice Period:
I1 = start date
K1 = end date

Inventory In/Out Dates:
E3 = received date
F3 = collected date

We charge a daily rate per item, some items are different rates and come in and out on different days.
Row 4 and 5 are calculating correct as per image but as soon as the received date is greater than invoice period start date, I can't seem to get the formula right.

Any help is much appreciated.