Calculate Date Difference

Hi Dears,
I request a help:
A1 = 01-Apr-2009
B1 = 02-Apr-2009
C1 = 03-Apr-2009
D1 = Blank
E1 = 05-Apr-2009
F1 = 06-Apr-2009

What is the best suitable formula to find "First blank cell", then "date difference" between today and its previous cell's (i.e today minus "C1" in this case).

Thanks & Best regards,
Ananthakrishnan.K

You can use macro (maybe even you don't...) to do that... this is my solution without macro...

If it's really blank...
Add aditional column...
Write: =IF(ISBLANK(F1);0;F1) and extend to whole array

Then your solution is:

=TODAY()-INDEX(G1:G11;MATCH(0;G1:G11;0)-1;1)
(I assume first value of array is date...)

Enter this array formula:

=TODAY()-INDIRECT(ADDRESS(1,MATCH(TRUE,ISBLANK(A1:F1),0)-1))

..and confirm with CTRL-SHIFT-ENTER to activate the array. If you do that, the answer 17 should appear. If you get an error, MAKE SURE D1 is really EMPTY (blank), then enter that formula again with CTRL-SHIFT-ENTER.

BTW, I wouldn't use a LOT of these array formulas, but for a small data set it's fine. If you have 1000s of rows to deal with, using the added helper ROW (not column) suggested by zbor is the way to go.

Yes, sorry, since I copy pasted it I dealed with columns

Or similar to method shown by JBeaucaire:

=TODAY()-INDEX(A1:A6,MATCH(TRUE,ISBLANK(A1:A6),0)-1)

also confirmed with Control+Shift+Enter

I try myself with INDEX-MATCH apporach but I keep getting #N/A error...

And now with both above formulas (regardless of ctrl+shift+enter)...

Perhaps your blank is no longer a true blank, what happends if you use:

=TODAY()-INDEX(A1:A6,MATCH("",A1:A6,0)-1)

Same :-S

It is true blank since I've deleted those cells

Book1.xlsx

Put into the right format, our formulas work fine.

Works.. Thx.. Nice job obth of you!

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Further, you can highlight the range that contains the date. Go to find + replace (CTRL+H), leave the find box empty, replace with ="". Hit replace all. That will change the blank cells such that they are not true blanks. The you can use:

=TODAY()-INDEX(A1:A6,MATCH("",A1:A6,0)-1)

Which does not have to be CSE entered and should be somewhat more efficient than any array formula method used.

I also find that solution but then I assume that dates might be entered manually so I thought this up might be better but I write If it's really blank... just in case that user can adopt formula without inserting new row in case there is something as =""