Auto populate Date

Carlson Hydraulics

"I need assistance with a formula in Excel. I'm trying to automatically calculate due dates in Column J based on the difficulty level specified in Column I. If the difficulty level is 'Hard,' I want the due date to be 15 business days from the received date in Column J. For 'Medium,' it should be 8 business days, and for 'Easy,' 4 business days. The formula I currently have is:

=IF(I1="Hard", WORKDAY(J1, 15), IF(I1="Medium", WORKDAY(J1, 8), IF(I1="Easy", WORKDAY(J1, 4), "")))

However, it doesn't seem to be working as expected. Any guidance on correcting this formula would be greatly appreciated."

KPH

Hi @Carlson Hydraulics

It looks like you are trying to use an Excel formula on a Smartsheet sheet. It won't work. There is no column I. Looking at the sheet your column is called Difficulty. So you need to replace I with [Difficulty]:[Difficulty] to refer to the whole column or [Difficulty]@row to refer to the cell in that column on that row. Similarly J is not J but [Due Date]. (note: the square brackets are only necessary if there are spaces in the column names)

You can find some intro to Smartsheet formulas courses at: https://help.smartsheet.com/

Fortunately, the Excel and Smartsheet IF and WORKDAY functions are very similar, so you don't have much to change (I have linked to the syntax for these functions so you can learn more). This should work:

=IF(Difficulty@row = "Hard", WORKDAY([Due Date]@row, 15), IF(Difficulty@row = "Medium", WORKDAY([Due Date]@row, 8), IF(Difficulty@row = "Easy", WORKDAY([Due Date]@row, 4), "")))

Carlson Hydraulics

I might have my Columns wrong.

KPH

Hi @Carlson Hydraulics

It looks like you are trying to use an Excel formula on a Smartsheet sheet. It won't work. There is no column I. Looking at the sheet your column is called Difficulty. So you need to replace I with [Difficulty]:[Difficulty] to refer to the whole column or [Difficulty]@row to refer to the cell in that column on that row. Similarly J is not J but [Due Date]. (note: the square brackets are only necessary if there are spaces in the column names)

You can find some intro to Smartsheet formulas courses at: https://help.smartsheet.com/

Fortunately, the Excel and Smartsheet IF and WORKDAY functions are very similar, so you don't have much to change (I have linked to the syntax for these functions so you can learn more). This should work:

=IF(Difficulty@row = "Hard", WORKDAY([Due Date]@row, 15), IF(Difficulty@row = "Medium", WORKDAY([Due Date]@row, 8), IF(Difficulty@row = "Easy", WORKDAY([Due Date]@row, 4), "")))