Skip to Content
Former Member
Oct 12, 2006 at 11:08 PM

Comparing dates


<p>I&#39;m not a devloper but some technical savy. I&#39;m having an issue with getting my formula to work. I don&#39;t think it&#39;s complex, just think I&#39;m too close to see the problem. Here is the setup.</p><p>I have records that have a unique ID to them - that&#39;s my key/grouping</p><p> What I want to do is there is multiple sub records for each ID. The records have a created date and an audit record written date. Sometimes there&#39;s a gap between when the record was created and when the audit record was written. I want a formula field to display on the report that reads through each audit record and calculates the lag time - that&#39;s actually easy - got that but what I&#39;m having trouble with is taking that end calculation result and using that as my starting time for the next calculation. More or less I want each record to have a concantinated time line when reading through all the sub records for the master record.</p><p>Maybe I can give an example.</p><p>Record 1 - </p><p>created at 9:00AM - Audit written at 9:05AM (audit record lag time 5 minutes)</p><p>Record status changed at 10:00AM - audit record written at 10;10 AM(audit record lag time 10 minutes)</p><p>Record closed at 11:00Am - audit written at 11:45 AM.(audit record lag time 45 minutes)</p><p>I want the field on the report to show the time between audit records written - problem is it&#39;s the same field being used for each record. I think what I need to do is do the first calculation - (audit record create date - issue create date)*1440 move to a variable. The next record should be (audit record create date - variable) *1440 add to variable < - - more or less a running total.</p>