Counting Blank Cells using TODAY()

[Joeclupis]

Hello all,

I have a spreadsheet that I am trying to count the number of days in one column from last entry to TODAY(). I have a date column and then a second column that has delivery amounts. This second column is ONLY used when a delivery is made. I would like to count the number of days from last delivery to TODAY(). I can get the CountBlank to work, but not as a floating count. Also, is there a way to make it so that the formula covers the entire column, but only returns from the last delivery thru current date?

Thank you for your help,

Joe

 

[AliGW]

Please provide some sample data for us to work with.

 

[Joeclupis]

Please provide some sample data for us to work with.

date	purchases
3/5/17	4500
3/6/17
3/7/17
3/8/17
3/9/17
3/10/17
3/11/17
3/13/17
3/14/17	8000
3/15/17
3/18/17
3/19/17
3/20/17
3/21/17	1600
3/22/17
3/23/17
3/24/17
3/25/17
3/26/17
3/27/17
3/28/17
3/29/17
3/30/17
3/31/17
4/1/17

<tbody>
</tbody>

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=TODAY()-MAX(IF(B2:B1000<>"",A2:A1000))

 

[Joeclupis]

Tetra201,

Thank you for the formula.

I made an adjustment as I am using the formula in one tab referring to another tab.

for some reason, I am coming up with a negative number.

Here is the amended formula: =TODAY()-MAX(IF('DAILY VALUES'!F2:F10000<>"",'DAILY VALUES'!A2:A10000))

F is the correct column for my delivery annotations, and A is the date column.

Should i be expecting negative numbers? I would expect a positive number as I am "counting" the number of days from the last delivery.

 

[Aladin Akyurek]

Just enter:

=TODAY()-LOOKUP(9.99999999999999E+307,B:B,A:A)

Format the formula cell as General.

 

[Joeclupis]

Aladin Akyurek,

I pasted the formula into the spreadsheet. into the column with the entries for deliveries. the count is -274. Unfortunately, the count should be 16. We had a delivery on 3/14/17.

 

[Aladin Akyurek]

Aladin Akyurek,

I pasted the formula into the spreadsheet. into the column with the entries for deliveries. the count is -274. Unfortunately, the count should be 16. We had a delivery on 3/14/17.

The formula will return a positive number unless you have delivery of the future byond today()...

See the workbook that implements the suggestion:
https://www.dropbox.com/s/mz2t9kbjypnhpb1/Joeclupis days from last delivery to today.xlsx?dl=0

 

[Tetra201]

@Joeclupis:

Try this (to be entered using Ctrl+Shift+Enter, not just Enter):

=TODAY()-MAX(IF(('DAILY VALUES'!F2:F10000<>"")*('DAILY VALUES'!A2:A10000)<=TODAY()),'DAILY VALUES'!A2:A10000))

 

[Joeclupis]

Aladin,

I pasted the formula into the worksheet in yesterday's row. I still get -274.