Auto populate Date
"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."
Best Answer
-
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), "")))
Answers
-
I might have my Columns wrong.
-
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), "")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!