Formula for Modified Date

davidmartin95826

Hello,

I want to keep track of End Date changes on workstream plans. Aware the automated Modified Date column is updated following any change to that row. I want to find a way to populate a Changes Log with specific information from a row, if the End Date Column has been updated. Unfortunately, the layout of the activity Log is too detailed for the type of view we're trying to present.

It would be great if there was a formula I could use to insert Todays Date into a hidden Column whenever the End Date Column has been updated. That way I know when the End Date was updated and not the entire row.

I tried inserting a Variance column (End Date - Baseline Date). Although if the End Date is changed a second or third time (which is possible), it won't pull through in either the Zap or Smartsheet Report.

Thanks again for your help,

David

Andrée Starå

Hi David,

Zapier is an excellent option for this scenario. It seems like you're already using it. Are you? You'd need to at least have the Starter Plan for it to work the way you need.

You could store the date, and you could also copy the row to another sheet to have a running log.

Would that work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

davidmartin95826

Hi Andree,

Zapier is great, we're using it for a number of things.

The only challenge I have is filtering the Zapier to only copy a new row across when the End Date is updated. Currently I'm using a variance column (End Date - Baseline Date) to only pull in changes to End Date.

Its currently copying rows across to the Changes Log if any column on that row is updated, and the variance exists. Which is perfect for the first date change, although if variance exists (i.e. End Date was changed at least once) and someone updates the Status column, then it pulls that row through to the Changes Log.

I hope that makes sense.

Thanks again for your help,

David

Andrée Starå

Happy to help!

If I understand you correctly, you would use filters in Zapier to decide what should copy or not.

Would that work?

Have a fantastic weekend!

davidmartin95826

Exactly, although I'm not sure which filter to use. I basically just want to keep a log of any Deliverables which 'End Date' has been changed.

 

Andrée Starå

I'd try using the filter (Date/Time) After and maybe reference the first date that was added to the End Date (the original). It depends on what the patterns of the date are. If the End Date doesn't change until today's date or similar, then it could be more straightforward, but otherwise, we'd probably have to have a reference column with the original date.

Make sense? What do you think?

Hope that helps!

davidmartin95826

Thanks Andree,

 

I'm not sure if that will work. Apologies in advance if I misunderstood your instruction. Thanks for your patience.

We have a Programme Roadmap with over 500 deliverables. Not all have been baselined, although any Deliverable's which have feature Baseline Date in the Baseline Column. The challenge is trying to create a weekly report which shows Date Changes. The standard reports within Smartsheet are almost too detailed, and I can't seem to extract those reports into excel to edit into another format. I need to include the report as a slide or two in PPT.

 

Ideally, for Governance Purposes I need to create a Changes Log which states the Before and Current End Date for each Milestone, along with when it was changed and modified by.

 

I've tried using variance column along the modified column gets updated as soon as someone updates the Status or RAG. If only there was a way to pull in a row if the Date changes and have a column stating the before change Date. Example below;

 

Deliverable Name   - Start Date - Previous End Date - Current End Date - Modified - Modified by  

Deliverables 1 -  01/01/2019 - 20/09/2019 - 29/09/2019 - 13/09/2019 - David

 

Thanks,

David

Cleversheet

Assuming you have a unique identifier for each Deliverable, could you use the CopyRow feature in Automation to copy a row to a separate Sheet triggered by each change to that row’s EndDate? This will store a record of frozen dates for each edit in that column which could then be reported on, or even brought back to the original Sheet using Index/Match. I haven’t thought thru how to identify and isolate the latest of multiple changes, but it might involve a MAX formula.

i realize this is not a solution, but could perhaps open a pathway to one.

Paul Newcome

I was thinking something similar to @cleversheet. Using a Copy Row Automation to copy the row any time that date changes would track the historical changes. On the sheet you are copying to, you can add in the "Created Date" system column to also track when each change was made. This would allow you to further cleversheet's solution of leveraging an INDEX/MATCH with a MAX statement and a unique ID to pull the most recent date/time stamp of the change.

Michael Williams

I've been successful using Zapier to capture updates to rows in a master sheet and copy the updated row using the to another sheet using the Updated Row trigger and the Copy Row actions respectively. Using the Modified Date column property would make a nice clean solution to find the last updated row using a MAX function against the Modified Date column in either sheet. However, for whatever reason, the results of the MAX function on the Modified Date column only returns the date and NOT the time. In our case, this isn't sufficient because we could have multiple updates to the same row in the same day and we need the time component of the Modified Date to find the last update.

Although I'm working on another solution for the last updated row, I'm still looking for a formula using the Modified Date that would return a result down to the minute.

Paul Newcome

@Michael Williams Here is a thread that has a number of time based solutions. There should be one tucked away in there that will help you convert the time into a numerical value which can then be leveraged in the MAX function.

(Edit to include link... 🤦‍♂️)

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time