=IF(ISBLANK($T2),"", MAX(0,$T2-TODAY()))

This formula reads column T for a date and counts down to Zero where it stops. There is a formula underlying the date in column T so that even when there is no date, Excel doesn't read the cell as "blank" because of the formula. I've tried ISERROR but I think I got my syntax wrong. I need this formula to return NULL when there is no date in column T.
Thanks.

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, 2 views)

I'll look at your table tomorrow then, as I don't have Office in my home computer.

But having your formula return #VALUE! error on the rows that must actually have dates may be because in column T are not dates at all, but datestrings!

=IF(LEN($T2)<2,"", MAX(0,$T2-TODAY()))


It's <2 in the formula because the formula for most cells in column T is:
=IF(ISBLANK($Q2)," ", DATE(YEAR($R2),MONTH($R2),(DAY($R2)-60)))
where there is a single space between the double quotes. If you had nothing between the double quotes the formula would be a more traditional:
=IF(LEN($T2)<1,"", MAX(0,$T2-TODAY()))


BTW, consider shortening the formula in column T to:
=IF(ISBLANK($Q2),"", $R2-60)

OK, I looked at your table, and yes, you are using a Defined Table there. But why have you all those empty rows there at all? With Defined Table, whenever you enter anything into any cell immediately below of currently last one, the Table is automatically expanding, and will include the row you entered this value into. Also all formats, Data Validation Lists, conditional formats, formulas, etc. are automatically applied for all cells in new Table row, so long as they are same for whole column. The same happens, when you activate rightmost cell in last row of your Table, and press Tab key.

Now about your formulas.
For column S, the working formula may be like
The formula in Table syntax looks longer, because you have unreasonably long header for column it is referring to, plus the header contains spaces, so for every reference an additional pair of brackets must be used, but with this syntax, you can move, add or delete columns in your Table without this affecting the formulas in any way, unless you delete a column some formula is referring to. Btw. You can also rename columns freely - all formulas in this workbook (and designed in Table Formulas format) will adjust automatically!

You also may consider to change your other formulas likewise:
For column R like
For column T like
(In this formula, there was no need to use DATE() function, as really dates are long integers, where value 1 represents a day, and adding or removing an integer value simply returns a date later or earlier this number of days (so long as result remains a positive number!)
etc.

In your original formulas, when the formula had to return empty string, you used a space string instead! I.e. you used " " instead "". Those are different values!

In deadline formula (in column R) you are adding 1 to year number. Let's assume the declaration date will be 29.02.2024 (a leap year). With current formula you get deadline as 01.03.2025. Do you really want this, or you'd prefer 28.02.2025? When yes, then the formula must be something like
(Because day 0 of any month is the last day of previous month!)

That long formula worked. I know I have long column header names, but for our purposes, it works. And the reason for all the blank rows is that I knew I would be adding multiple lines of data just a soon as I got the formulas right, though, of course, you are right about the table expanding to accommodate new rows, just kind of did it out of habit.
Thanks for your help...

Please mark thread solved ( see thread tools)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post