#Invaldi Operation when a date dell is blank

Kevin7859

Trying to analyze multiple date columns against a current date to ensure the training has occurred within the last year and if not then flag as needing to be trained. But when I incorporate the various date cells for reference I get the #invalid operation error for the blanks.

Formula: =IF(OR([DTMC & Trainer Training Date]@row <= [Current Date]@row, [SSS Training Date]@row <= [Current Date]@row, [SESIR Training Date]@row <= [Current Date]@row, [DTMC Training Date]@row <= [Current Date]@row, [Trainer Training Date]@row <= [Current Date]@row), "Green", "Red")

The ones that are showing green are because I limited to just that cell to ensure the formula worked at an individual row level, but the others are using the full formula above.

Paul Newcome

So if any cell that contains a date has a date that is less that [Current Date]@row, flag it as "Green"?

Try this:

=IF(IFERROR(MIN(COLLECT([First Date Column]@row:[Last Date Column]@row, [First Date Column]@row:[Last Date Column]@row, ISDATE(@cell))), [Current Date]@row + 1) <= [Current Date]@row, "Green", "Red")

But keep in mind, the above (and the logic you used in your formula) won't quite capture what you described as wanting. These formulas will flag green for any date that is less than or equal to [Current Date]@row (meaning red for anything in the future), but it sounds by your description, that you want green for anything within the past year. In that case you want to flag green for any date that is greater than or equal to a year ago which would look more like this:

=IF(IFERROR(MIN(COLLECT([First Date Column]@row:[Last Date Column]@row, [First Date Column]@row:[Last Date Column]@row, ISDATE(@cell))), [Current Date]@row + 1) >= DATE(YEAR([Current Date]@row) - 1, MONTH([Current Date]@row), DAY([Current Date]@row)), "Green", "Red")

Paul Newcome

Ok. Give this a try:

=IF(COUNTIFS([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell)) = 0, "output if all blanks", IF(IFERROR(MIN(COLLECT([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, [SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell))), [Current Date]@row + 1) >= DATE(YEAR([Current Date]@row) - 1, MONTH([Current Date]@row), DAY([Current Date]@row)), "Green", "Red"))

Paul Newcome

Happy to help. 👍️

Paul Newcome

What would you expect to have output if a date is blank?

Kevin7859

It should output the date for only the column that has a date, and ignore the blanks.

Kevin7859

I tried to expand the formula to:

=IF(OR([SSS Training Date]@row <= [Current Date]@row, [SESIR Training Date]@row <= [Current Date]@row, [DTMC Training Date]@row <= [Current Date]@row, [Trainer Training Date]@row <= [Current Date]@row, [DTMC & Trainer Training Date]@row <= [Current Date]@row, [SSS Training Date]@row = "", [SESIR Training Date]@row = "", [DTMC Training Date]@row = "", [Trainer Training Date]@row = "", [DTMC & Trainer Training Date]@row = ""), "Green", "Red")

Paul Newcome

So if any cell that contains a date has a date that is less that [Current Date]@row, flag it as "Green"?

Try this:

=IF(IFERROR(MIN(COLLECT([First Date Column]@row:[Last Date Column]@row, [First Date Column]@row:[Last Date Column]@row, ISDATE(@cell))), [Current Date]@row + 1) <= [Current Date]@row, "Green", "Red")

But keep in mind, the above (and the logic you used in your formula) won't quite capture what you described as wanting. These formulas will flag green for any date that is less than or equal to [Current Date]@row (meaning red for anything in the future), but it sounds by your description, that you want green for anything within the past year. In that case you want to flag green for any date that is greater than or equal to a year ago which would look more like this:

=IF(IFERROR(MIN(COLLECT([First Date Column]@row:[Last Date Column]@row, [First Date Column]@row:[Last Date Column]@row, ISDATE(@cell))), [Current Date]@row + 1) >= DATE(YEAR([Current Date]@row) - 1, MONTH([Current Date]@row), DAY([Current Date]@row)), "Green", "Red")

Kevin7859

thank Paul, that worked, I just had to make one change to set the < = to be >= to flag any dates that were older than one year as red

Kevin7859

=IF(IFERROR(MIN(COLLECT([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, [SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell))), [Current Date]@row + 1) >= [Current Date]@row, "Green", "Red")

Paul Newcome

Happy to help. 👍️

Kevin7859

One last item Paul, how can I modify to allow for any cell that is blank for those positions that do not require any of the trainings listed in the table. We have some folks who do not require training so those dates will be blank.

Paul Newcome

The above should do that by leveraging the ISDATE function to "collect" only the fields that have dates in them then pulling the oldest one of those dates using the MIN function which is then compared to [Current Date].

Kevin7859

I get #Invalid Operation for the row where there are no dates listed, because the role doesn't require training.

=IF(IFERROR(MIN(COLLECT([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, [SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell))), [Current Date]@row + 1) >= DATE(YEAR([Current Date]@row) - 1, MONTH([Current Date]@row), DAY([Current Date]@row)), "Green", "Red")

Paul Newcome

Ok. Give this a try:

=IF(COUNTIFS([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell)) = 0, "output if all blanks", IF(IFERROR(MIN(COLLECT([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, [SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell))), [Current Date]@row + 1) >= DATE(YEAR([Current Date]@row) - 1, MONTH([Current Date]@row), DAY([Current Date]@row)), "Green", "Red"))

Kevin7859

Thanks Paul, I tweeked it to give a yellow output for the all blanks

=IF(COUNTIFS([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell )) = 0, "Yellow", IF(IFERROR(MIN(COLLECT([SSS Training Date]@row:[DTMC & Trainer Training Date]@row, [SSS Training Date]@row:[DTMC & Trainer Training Date]@row, ISDATE(@cell ))), [Current Date]@row + 1) >= DATE(YEAR([Current Date]@row) - 1, MONTH([Current Date]@row), DAY([Current Date]@row)), "Green", "Red"))@cell

And for the comparison of the 2 symbol columns I used

=IF(OR(AND([Category Trained]@row = "Green", [Trained w/i Last Year]@row = "Green"), AND([Category Trained]@row = "Blue", [Trained w/i Last Year]@row = "Yellow")), "Yes", "No")

Paul Newcome

Happy to help. 👍️