An prefect example why I never use row below the last one of the table for any information - be it formula, or some manual entry!

Instead I put such info at top of sheet - above table headers. An additional bonus is, that when I apply Freeze Panes feature to have column headers always be shown, this info at top of page will always visible too.

As I have all my tables Defined Tables, it will be easy to calculate any counts or totals or whatever for any column in row(s) reserved for them at top of page. In case you use regular Excel tables, you either define your dataranges of your table columns as dynamic ranges, or you have to refer in totals formula for range reahcing down enough to work for some amount of time (e.g. your table has for column C values in range $C$5:$C$9, but to get the sum of this table column you calculate it for range $C$5:$C$1000), and you keep the cells below you table free of any additional info.

About your current problem, in case the formula returns a non-numeric value, you can check for this easily. Like
= IF(ISNUMBER($T2),MAX(0,$T2-TODAY(),"")
NB! You see that you don't need to check the cell being empty separately, as the value of empty cell's is not numeric!

Also, in case the cell below table contains e.g. count (which is reasonable, as I can't see how you want to sum all dates), you can check for the number being too small to be a reasonable date. E.g. when you are sure in your table will never be any dates from previous century, this will probably work for you
= IF(1*$T2>36525,MAX(0,$T2-TODAY(),"")


NB! Again you don't need to check the cell being empty separately, as the value of empty cell multiplied by any number is 0, i.e. is not greater than 36525!

And as last - why have you copied your formula down to this wrong row at all? From your example formula, your calculation is based on current row of formula! An afterthought - when it is because you use a Defined Table, and the formula is expanding automatically and counting the last row having any data as part of Table, then read the top rows of my answer!

Sorry, but that didn't work at all. Got a #VALUE! error on the rows that actually have dates in the column T cell, and a FALSE error on the rows that don't have dates (but have formulas).

I've attached the file.

The worksheet will have a variable number of rows that contain data so I need the formula to appear in all the table rows but not return an error message if it has no date. I've done this successfully with other formulas but somehow am missing it with this one.

Attached Files

ProjectTrackerTest.xlsx (47.3 KB, 4 views)