Date Subtraction from Blank Cells

[CappyT]

Greetings,

I have =Today() in cell C2 which is working fine. In column Z I have the dates of when a task has been accomplished. In column AA I have it calculating how many days it has been since the task was accomplished. So if cell Z6 has an accomplished date then cell A6 is =Z6-C2 which has been working fine. However, when a row in the Z column doesn't have an accomplished date IE the task is still pending then column AA outputs -43045. I assume it has something to do with the C2 (date cell) subtracting from a blank cell in column Z. I would like if the Z cell is blank for AA column's output to also be blank opposed to -43045. I tried using the following IF statement but it just outputs the same as before. =IF(ISBLANK(Z6),"",Z6-C2) Any ideas of what am I doing wrong? Thanks for the help.
V/R,
CappyT

 

[FDibbins]

Try this...
=if(Z6="","",Z6-C2)

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Mon 06 Nov 2017) is actually 43045

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
So -43045 is excels way of trying to show a negative date - which it does not deal with very well

edit: fyi =IF(ISBLANK(Z6),"",Z6-C2) worked just fine for me? Would Z6 by chance contain a formula? If so, it is not blank

 

[CappyT]

Yeah that makes sense thank you for explaining. But yes I didn't even think to take that into account. Column Z does have code already. VLOOKUP function referencing a separate tab to pull the date from. An example is like =VLOOKUP(A6,importSMR!$A$1:$BG$287,44,0) I have another tab that I input a downloadable spreadsheet from an online database we are using. How would I add the code you provided considering column Z has the VLOOKUP function included instead of just being blank as I previously stated?

 

[FDibbins]

If you are still testing the cell to see if it has (returned) nothing, my suggestion should still work...
=if(Z6="","",Z6-C2)

 

[Aladin Akyurek]

Why don't you test for a non-zero number since a date is a number?

=IF(ISNUMBER(1/Z6),Z6-C2,"")