I would like to create a N/A remark for the blank dates based on the grey status of an adjacent cell

Ronnie D.

Formula for OSHA-30 Status: =IF(ISBLANK([OSHA-30 Exp Date (10-yr)]@row), "Gray", IF([OSHA-30 Exp Date (10-yr)]@row - TODAY() > 75, "Green", IF([OSHA-30 Exp Date (10-yr)]@row < TODAY(), "Red", "Yellow")))

Formula for OSHA-30 Exp Date (10-yr): =[OSHA-30 Training Date]@row + 3650

Thank you!

Heath Hilton

Ronnie D.,

Well I figured out a way to do it but it may take an extra column to do it. The problem is that the "OSHA-30 Training Date" column is fighting because you need it to be a dealt with as if it was a "date" so you can add 10 years to help calculate your 10 year date, but then you also want to flip it back to a "text" field and just display regular text.

So here is how to do it.

You would need to convert your "OSHA-30 Training Date" to a "date" and then make sure "Restrict to dates only" deselected.

Then put a formula in it - =IF([OSHA-30 Status]@row = "Gray", "N/A", [Manual Date]@row)

and then "convert it to a column formula" and then "Lock" the column.

The Expiration Date column would be unchanged.

The "Manual Date" is the new one. This is the column that would actually have the date entered into it. It would be perfect if you could hide this column and just enter the data via a form but if you were typing these in, it would need to be visible.

Basically what is happening is you would type in the date into the Manual Date column and the 10 year calculates off of that, which then is checked out by the Status column. The Training Date column is always looking to see if the Status is "Gray" and when it is not, displays the value of "Manual Date".

Hope this help.

Heath Hilton