Copy a date from one cell to another when a status changes
I am looking for a formula to to copy a date from one cell to another when the status changes to "In Progress". The formula below works but every time the date in cell 1 changes it changes in cell 2 because the status is "In Progress". I only want the date to copy to cell 2 when the status changes to "In Progress" the first time and not change going forward if the the date in cell 1 changes. I want to copy the date from Cell 1 to Cell 2 and freeze the date in Cell 2. To be clear the date I want to copy is not the date the was put "In Progress"
Cell 1 "Due Date"
Cell 2 "Original Due Date"
I have the following formula in Cell 2
=IF(Status@row = "In Progress", [Due Date]@row)
Answers
-
I hope you're well and safe!
Please have a look at my post below with a method I developed.
More info:
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I will play with that latter today and see if I can get that to work.
Last night I was trying playing around with the following that I thought would would work but not having much luck.
I added a check box column and used the automation to check the box when the status changes to "In Progress" I thought if I used the following formula the date would be copied only when the check box was not checked. My thought was that as soon as the the status changes to "In Progress" the formula would run and the date would be copied. Once you save the sheet the check box would be checked by automation and the formula would not run if the box was checked but I just can't get it to work.
Cell 1 "Due Date"
Cell 2 "Original Due Date"
Cell 3 "In Progress" (Check Box)
I have the following formula in Cell 2
=IF(Status@row = "In Progress", AND([In Progress]@row, 0), [Start Date]@row
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!