automation to update a date field to new value
Hi,
I am trying to set up an automation where it detects when a date found in column DATE is passed
and then would automatically update it based on the value found in the column frequency
for example :
if the DATE = 01/07/2021
and the frequency = Weekly
then when we pass the date 01/07/2021 it should automatically change the DATE field to 08/07/2021
I am able to calculate the new date value using a formula
But for some reason I can not figure out how to automate the DATE field being updated to this new value.
你们建议如何可以做什么?
Answers
-
Bassam Khalil ✭✭✭✭✭✭
Hope you are fine, you can do that as following
1- create a helper column "Check Box" to calculate when you pass the date use call it "Date Pass" the following formula
Date Pass =IF([email protected]- TODAY() = 0, 1, "")
2- Create a column called "New Date" Date type to calculate the new date and use the following formula
New Date =IF([Remaining To Pass]@row = 1,[email protected]+ 7,[email protected])
and you can use the new date column to show up-to-date Dates as per your request.
the following screenshot shows the result:
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
非常感谢你尝试回答my question .
Sadly I fear i have not been clear about the actual problem.
1) I succeeded in calculating the next date ==> OK
2) I managed to make an automation that detects that the DATE has passed. ==> OK
3) but when I when I want this automation to replace the DATE with the newly calculated date
I do not know how. ==> PROBLEM
The reason I want this original date to be replace is that it is used for the calendar view.
So for example when i have a recurring action in my project, I want the date to be moved every time so this action appears on the new date in the calendar view each time.
Example :
we have this repeated task that will happen on the date mentioned in DATE column
So when this date is passed the FUTURE date should be copied over the DATE FIELD and a new FUTURE date can be calculated
I tried to do this via automation but here is where i get stuck
-
Garrett Henke ✭✭✭✭✭
Steven,
This is a possible work around i can think of but I'm not 100% sure that it will work as it is basically a continuous loop.
Set up a checkbox column to the right of the Extra Days column.
- Formula for the checkbox column =IF([Extra Days]@row=0,1,0)
- (This will check the box when extra days is equal to zero)
- Next set up a Record a Date workflow automation
- When checkbox column is checked Record a Date in Date column
- Set this to reoccur every day at 7pm (or any time of your choosing)
Give this a shot because I have a feeling that it will work for what you are trying to do. Best of Luck!
- Formula for the checkbox column =IF([Extra Days]@row=0,1,0)
Help Article Resources
Categories
Check out theFormula Handbook template!
If I understand correctly, this may help.<\/p>
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107116,"type":"question","name":"I'd like to create a column formula that references a specific cell with acceptable syntax","excerpt":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…","snippet":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to…","categoryID":322,"dateInserted":"2023-06-29T23:48:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T22:48:54+00:00","insertUserID":163028,"insertUser":{"userID":163028,"name":"jcabaniss","url":"https:\/\/community.smartsheet.com\/profile\/jcabaniss","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-01T02:58:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3376249626,"url":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","format":"Rich","lastPost":{"discussionID":107116,"commentID":383416,"name":"Re: I'd like to create a column formula that references a specific cell with acceptable syntax","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383416#Comment_383416","dateInserted":"2023-06-30T22:48:54+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T15:52:25+00:00","dateAnswered":"2023-06-30T00:15:36+00:00","acceptedAnswers":[{"commentID":383245,"body":"