Looking to set a Date Field based on contents of another field.
We have a sheet with a list field identifying the target fiscal year and quarter that we want an activity to start eg. FY 23 Q1, FY 23 Q2 etc.
This sheet also has been set up to work as a Gantt view with Start, Finish, and Duration as well.
I'd like to set the start date automatically based on the contents of the FY/Q field. I.E> When the contents of the FY/Q field are set to FY 23 Q1, the Start field is set to 10/1/2022 (October start of FY).
Is this possible? If so how so?
Thanks in advance,
Hudson
Best Answers
-
Hi @Hudson_TMR
This is possible as long as you do not have Dependencies enabled in your Project settings, and are only using the Start/End dates to have a visual in Gantt.
See: Areas where formula use is restricted
If you're not using predecessors / dependencies, then you could write an IF statement that outputs a date based on your FY Quarter column:
=IF([FY/Q]@row = "FY 23 Q1", DATE(2022, 10, 01), IF([FY/Q]@row = "FY 23 Q2", DATE(2023, 01, 01)...
To have your End Date automatically calculate based on this Start Date, you'll want to use the WORKDAY Function. I would personally have the Duration column be a manual number input, then use this in the WORKDAY function to find the End Date, like so:
=WORKDAY([Start Date]@row, Duration@row)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Genevieve-
Thank you for the response. I had not processed that there may be a work around to lift the dependencies off the sheet to make it work. I'll give it a shot.
Appreciate the help.
Cheers,
Hud
Answers
-
Hi @Hudson_TMR
This is possible as long as you do not have Dependencies enabled in your Project settings, and are only using the Start/End dates to have a visual in Gantt.
See: Areas where formula use is restricted
If you're not using predecessors / dependencies, then you could write an IF statement that outputs a date based on your FY Quarter column:
=IF([FY/Q]@row = "FY 23 Q1", DATE(2022, 10, 01), IF([FY/Q]@row = "FY 23 Q2", DATE(2023, 01, 01)...
To have your End Date automatically calculate based on this Start Date, you'll want to use the WORKDAY Function. I would personally have the Duration column be a manual number input, then use this in the WORKDAY function to find the End Date, like so:
=WORKDAY([Start Date]@row, Duration@row)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Genevieve-
Thank you for the response. I had not processed that there may be a work around to lift the dependencies off the sheet to make it work. I'll give it a shot.
Appreciate the help.
Cheers,
Hud
-
Decided to make a separate sheet with the values of FY and Q and the associated dates and use VLookup instead of nested IF's. Works like a charm.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!