IF formula where the output is the date from another column

swirt009

Hi All,

I'm trying to create an IF formula where the output is the date from another column.

In my organization, employees have 10 recertification due dates. I need the smartsheet to display which recertification is next, and when the due date is.

I know there will need to be multiple IF statements in the formula, but the example I'll use is just for the 2nd recertification.

I have three columns, Next Task Due, Next Task Due Date, and 2nd Recertification Due Date.

The formula would need to read IF the "Next Task Due" column is "2nd Recertification" then insert the date from the "2nd Recert Due Date" column into the "Next Task Due Date" column. Here is the formula I'm trying to use: =IF([Next Task Due]@row = "2nd Recertification", [2nd Recert Due Date]@row). However it returns a "#INVALID COLUMN VALUE" I've also included a screenshot.

Does anyone know what I'm doing wrong?

Thanks!

Brent Wilson

My guess is that the "Next Task Due Date" Column is not set as a Date Column..

this will cause an error as you are trying to pass a date parameter into a "Text" Field.

Also try this

=IF(Left( [Next Task Due]@row,1) = "2", [2nd Recert Due Date]@row, IF(Left( [Next Task Due]@row,1) = "3", [3rd Recert Due Date]@row, IF(Left( [Next Task Due]@row,1) = "4", [4th Recert Due Date]@row, IF(Left( [Next Task Due]@row,1) = "5", [5th Recert Due Date]@row, IF(Left( [Next Task Due]@row,1) = "6", [6th Recert Due Date]@row, IF(Left( [Next Task Due]@row,1) = "7", [7th Recert Due Date]@row, IF(Left( [Next Task Due]@row,1) = "8", [8th Recert Due Date]@row, IF(Left( [Next Task Due]@row,1) = "9", [9th Recert Due Date]@row, [10th Recert Due Date]@row ))))))))

This will select your column based on the first digit in the Next Task Due Date..

Might need to rejig it if you have a 1st Recert Date as I did not include that in the logic and be careful as 1 and 10 can get confused.. by the Left function I have

swirt009

Thanks, Brent!

This is the formula that worked.

=IF([Next Task Due]@row = "Observation", JOIN([Initial Certification & Observation Due Date]@row), IF([Next Task Due]@row = "Initial Certification", JOIN([Initial Certification & Observation Due Date]@row), IF([Next Task Due]@row = "1st Recertification", JOIN([1st Recert Due Date]@row), IF([Next Task Due]@row = "2nd Recertification", JOIN([2nd Recert Due Date]@row), IF([Next Task Due]@row = "3rd Recertification", JOIN([3rd Recert Due Date]@row), IF([Next Task Due]@row = "4th Recertification", JOIN([4th Recert Due Date]@row), IF([Next Task Due]@row = "5th Recertification", JOIN([5th Recert Due Date]@row), IF([Next Task Due]@row = "6th Recertification", JOIN([6th Recert Due Date]@row), IF([Next Task Due]@row = "7th Recertification", JOIN([7th Recert Due Date]@row), IF([Next Task Due]@row = "8th Recertification", JOIN([8th Recert Due Date]@row), IF([Next Task Due]@row = "9th Recertification", JOIN([9th Recert Due Date]@row), IF([Next Task Due]@row = "10th Recertification", JOIN([10th Recert Due Date]@row)))))))))))))