Skip to main content

Date Change Tracking

A
Forum|alt.badge.img+3

Hello, community! I need to create a formula (or find some other way) to keep track of changes to individual cells in "column A" via a second column that auto-populates with first entries in column A but does not change if column A changes after that. 

So for example:  

Column A = Project Start Date

Where a user manually enters the date they begin a project.

Column B = Original Project Start Date

Where it auto-populates with all existing dates in Column A + new dates added to empty cells

But does not update itself when a user manually changes the date in Column A after that. 

The goal is to be able to allow users to adjust or move out their start dates as needed but also keep a record of what the original date was without adding any other manual steps. I really hope this makes sense! TYIA for any help or ideas!

 

AMcAT_23

3 replies

seferiannie
Forum|alt.badge.img+5
  • seferiannie
  • Participating Frequently
  • 12 replies
  • April 1, 2024

you can usually track this in the history, but formula of this would counter itself (it's making itself void while adding more elements to it)

A
Forum|alt.badge.img+3
  • AMcAT_23
  • Author
  • New Participant
  • 1 reply
  • April 2, 2024
seferiannie wrote:

you can usually track this in the history, but formula of this would counter itself (it's making itself void while adding more elements to it)


Hi seferiannie, thank you đź™‚
Using the history to track it is way too time consuming, unfortunately, as there are hundreds of projects to track. Opening each record and scrolling all the history for each one is impossible.

I did come up with a different approach than what I originally asked about though. It’s clunky and not exactly what I need, but it works. I'll add it here in case anyone can use it or build from it for what they need:

A “Start Date” field: Manual user input
A “Start Date Created” field: CREATED_TIME(Start Date)
A “Start Date Modified” field: “Last modified time” function, with “Start Date” field as the only specified field
And a “Start Date Modified <10” field that uses this formula:

IF({Start Date Created}={Start Date Modified}, BLANK(),
IF(WORKDAY_DIFF({Start Date Modified},TODAY())>=10,BLANK(),
IF(WORKDAY_DIFF({Start Date Modified},TODAY())<=10,
WORKDAY_DIFF({Start Date Modified},TODAY())
)))

It makes those dates that have changed within the last 10 days easy to spot check, at least.
Cheers 🙂

AMcAT_23
I
1 person likes this
  • I
    itatvue
I
Forum|alt.badge.img+2
  • itatvue
  • New Participant
  • 4 replies
  • July 22, 2024

This is interesting.  Thanks for sharing.

Reply


Most helpful members this week

Terms & Conditions