Invalid Data Type after automation runs for TODAY HELPER

Terry Ables

Greetings,

I have a simple rollup Sheet that all formulas change to #Invalid Data Type after my "Today Helper" automation runs. If I open the sheet and save, it goes back to working (no changes). I have no TODAY() functions AND I am using a Today Helper column.

Example of the formula.

=SUMIFS({Safety Incidents Range (Recordable)}, {Safety Incidents Range (Date)}, MONTH(@cell ) = [Month No]@row, {Safety Incidents Range (Date)}, YEAR(@cell ) = YEAR([Today Helper]@row))@cell

Thanks,
Terry

Paul Newcome

Try using the TODAY function instead of referencing the helper column.

Terry Ables

Today() vs the Helper Column doesn't not fix the issue.

ker9

When does your Today Helper automation run? Mine runs at 2am (records the date at 2am) and I'm not having the same problem.

Is the Today Helper column is a Date column and has not been accidental changed?

We can't see the formula - are there any other dates involved?

Terry Ables

Automation for the helper column. Column is Date data type. Formula is listed in the original post.

ker9

I don't have a qualifier for when it's in the past - it just replaces all the dates with the new date.

Sorry, missed the original formula. Is it possible the problem is with the Safety Issues Date reference? Are all the columns with Dates set as Date columns?

(I sometimes manually run the Today automation and that does not affect my formulas although it takes a minute to load everything.)

Paul Newcome

I am leaning towards an issue with the data in the {Safety Incidents Range (Date)}. Try wrapping the YEAR and MONTH functions in IFERROR statements.

IFERROR(YEAR(@cell), 0) = YEAR(TODAY())