How to display "#N/A" error if the date value in a cell is greater than TODAY()?

[Mr. Snrub]

I have the following formula in a table column that prints a sequence of dates starting at 3/21/2018:

=IF(ROW()-ROW(Table1[#Headers])=1,DATE(2018,3,21),OFFSET([@Date],-1,0)+1)

I just want to add to that formula "if the current date to be displayed will be greater than today, then display the N/A error." How do I do that? The closest I can come up with is to duplicate the entire if statement in the "value if false" section of the outer if statement, like this:

=IF(IF(ROW()-ROW(Table1[#Headers])=1,DATE(2018,3,21),OFFSET([@Date],-1,0)+1)>TODAY(),NA(),IF(ROW()-ROW(Table1[#Headers])=1,DATE(2018,3,21),OFFSET([@Date],-1,0)+1))

...but that seems needlessly complex. Is there a simpler way to do this?

 

[Joe4]

Since you have Excel 365, you can avoid doing redundant calculations in your formulas by using the LET function.
Take a look here for an explanation and examples: How to use the Excel LET function | Exceljet

 

[Peter_SSs]

For a start, I would be trying to avoid the volatile function OFFSET.

What about this in the first cell of your data column?

=LET(d,DATE(2018,3,21)+ROW()-ROW(Table1[#Headers])-1,IF(d>TODAY(),NA(),d))

 

[Mr. Snrub]

Thank you, that worked great.

I see now that OFFSET() isn't needed here, since the cell values of one cell are not dependent on the cell directly above, the value can be derived from the row number of the current cell and the starting value. But other times OFFSET() is unavoidable, like so:

is "=row()-row([#Headers])" the best way to get auto-incremented sequential numbers in an Excel table?

I use the formula "=row()-row([#Headers])" to get an auto-incremented column of numbers. Is this the best way to do this? I think making two calls to the "ROW()" function is sub-optimal.

www.mrexcel.com

 

[Peter_SSs]

Thank you, that worked great.

You're welcome. Thanks for the follow-up.