Date Modified Calculation Help

[Joseph31]

Hi all,

Simple formula that I do not know how to wright. I am tring to come up the a formula that will put the date modified in a cell when a certain feild changes.

IE:

I have a Broker Field when a user puts or changes this value a new date (the current date) gets added to the date field called broker date.

Can this be done?

Joseph

[mr_vodka]

Joseph, I'm not sure if this is exactly what you are looking for, but take look at this thread for some ideas.

Ideas

[LaRetta]

Hi Joseph,

I'm unsure if you could spot the correlation in the sample John provided; it's quite a bit different. I think this might more directly relate ... Add this as an Auto-Enter by Calculation to your Broker Date field. However, it can NOT be a real date field as date fields will not allow multiple dates so make it text. Be sure to uncheck box 'Do Not Replace Existing Value.'

If ( not IsEmpty ( Broker ) and LeftValues ( BrokerDate ; 1 ) ≠ Get ( CurrentDate ) & ¶ ; Get ( CurrentDate ) & ¶ & BrokerDate ; BrokerDate )

What this will do:

1) If the Broker field is entered but nothing is changed, no date is added.

2) If anything is changed in Broker AND if the current date is not exactly the same as the TOP date in Broker Date (each new date is added to the top), add the current date at the top of the Broker Date field.

3) If anything is changed in Broker and the current date is already at the top of the Broker Date field, it is not added again. This protects from producing something which looks wonky (in case User edits, leaves field, edits again), such as:

11/3/2005

11/3/2005

11/3/2005

11/2/2005

11/2/2005

If you wish to know how many times it's modified in one day, consider using Stamp instead (which would produce a tighter modification history). You would then use:

If ( not IsEmpty ( Broker ) ; Get ( CurrentTimestamp ) & ¶ & BrokerDate ; BrokerDate )

Give this a try. Remember Broker Date must be TEXT. And (to avoid confusion in the future), I would rename Broker Date to something like BrokerTextDate (or BrokerDateHistory) so you know it's not a REAL date.

LaRetta