Difference between two dates where some cells are blank

[aglawrence]

Hi everyone,

In column 'B', I have dates in some cells (e.g. "21/10/2017"), whereas other cells are blank.

In column 'A', I have the formula "=A2", =A3", etc.

In column 'I' is the formula "=TODAY()-A2" (or ="TODAY()-A3", and so on). This works fine where column 'A' contains a date, but when column 'A' contains no displayed data, column it displays "43034".

I need column 'I' to display either a number ("5" for example), or to be blank.

Can anyone help with this? In my case, what I refer to above as 'Column B' is actually on a different sheet within the same workbook, I have just simplified things for the sake of this example).

Thanks and regards,

Andy

[63falcondude]

I'm confused about the formula in column A. Nevertheless, this should do what you are looking for.

I2 =IF(A2="","",TODAY()-A2)

That is: "If column A is blank then return blank, otherwise return TODAY()-A2."

[Glenn Kennedy]

Yes. It might be easier to follow with a sample sheet (always a VERY good way of getting a rapid response).

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[aglawrence]

Thanks 63falcondude,

Sorry, that was a typo:

"In column 'A', I have the formula "=A2", =A3", etc."
Should read:
In column 'A', I have the formula "=B2", =B3", etc.

I tried the formula you suggested, but it displays "43034" in rows where column 'A' is 'blank'. 43034 is today's date, formatted as a number. Please note that, as mentioned above, the 'blank' cells in column 'A' contain a formula. If I delete the formula so the cell is empty as opposed to just not displaying anything, your formula works fine and gives me a blank cell in column 'I'.

[63falcondude]

It's difficult to diagnose without seeing a sample spreadsheet.

Try this instead:

I2 =IF(B2="","",TODAY()-A2)

If this doesn't work, please upload a small representative sample of your workbook along with the desired outcome of the formula.

[aglawrence]

Please see attached example.

[Glenn Kennedy]

Try:

=IF(A2=0,"",TODAY()-A2)

[63falcondude]

You have something going on in your 'Orders' worksheet that displays 0 as blank. I think that has something to do with the issue.

When I move the data into a new worksheet, the formula suggested in post #2 works as expected.

[Glenn Kennedy]

That said... it's advsable to use this in A2, copied down:

=IF(Accounts!A2>0,Accounts!A2,"")

and this in I2, copied down:

=IF(A2="","",TODAY()-A2)

otherwise you'' run into the same problem later...

[sandy666]

try this one: =IF(LEN(A2)=1,"",TODAY()-A2) and copy down

[Glenn Kennedy]

I beg to disagree Sandy. It's better to get rid of the problem at source... as per Post 9

[aglawrence]

Thanks everybody for your input, I really appreciate it. sandy666, I went with your solution and it works perfectly.

Out of interest, can anyone offer a brief summary of how the LEN function is used? I have never come across it before, plus it might be helpful to anyone who reads this post in the future.

[63falcondude]

aglawrence, I am assuming that when trying to pull the dates, some of the cells showed up as 0 so you changed the settings for the entire worksheet to make 0 show as blank.

You could have done that in the formulas. Otherwise, you may (and probably will) experience unexpected issues in the future (like you did here).

[aglawrence]

Thanks 63falcondude, that's a good point.

Glenn, your point in post #9 does make sense and I will test this out shortly.

[sandy666]

Thanks everybody for your input, I really appreciate it. sandy666, I went with your solution and it works perfectly.

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.

[sandy666]

Here is extended: =IF(OR(AND(CELL("format",A2)="D1",LEN(A2)=1),ISBLANK(A2),A2="")),"",TODAY()-A2))
because of date format, len, real blank and empty string