Automate dates
Hi, I'm trying to automate dates being filled in based on a certain schedule:
Basically C1D1 is day 1 and C1D2 is day 2 and so forth...
I'm envisioning a formula that reads "if [Visit #]@row contains D2 (or whatever day), then add 1 day or 2 days, etc. to day 1.
I have part of the formula:
=IF(CONTAINS("D2", [Visit #]@row), Planned2 + 1)
But I'd like to optimize the formula so that I can make a column formula and it will take the ((day x-1) + day 1). I think I need a LEFT function somewhere.
Thanks for your help!
Best Answer
-
My suggestion would be a parent row that houses the Planned date you want to lock in. Then you can reference PARENT(Planned@row).
Answers
-
=value(right([visit #]@row,len([visit #]@row) -3))+planned2
-
Thank you for the formula, you definitely put me on the right track. I modified it to get the correct dates:
=VALUE(RIGHT([Visit #]@row, LEN([Visit #]@row) - 3)) + Planned2 - 1
While this formula works, I needed the "Planned2" to be constant, just like if you did $C$1 in excel to keep the cell in a formula constant.
This formula works perfectly.
=VALUE(RIGHT([Visit #]@row, LEN([Visit #]@row) - 3)) + $Planned$2 - 1
I'd like to optimize this even further if possible:
You'll notice that there are several "CxD1" in this scenario. I need the CxD2, D3, D8, D15 to build on the respective CxD1. Any suggestions on how to modify the above equation?
Thanks again for your help!
-
I tried this but it didn't work:
=VALUE(RIGHT([Visit #]@row, LEN([Visit #]@row) - 3)) + IF(CONTAINS("D1", [Visit #]@row), [Visit #]@row - 1)
-
My suggestion would be a parent row that houses the Planned date you want to lock in. Then you can reference PARENT(Planned@row).
-
Thank you so much! This was really helpful!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!