Totalling Time

[hoops]

I have created a time billing db with an auto create time start and an auto create modification time.

How do I script in order to total the time taken between the 2 values and have that as an auto field when the modification field is populated.

[RussBaker]

No need to script it, a calculation field will do it for you. The Time_Diff field must be in Time format.

code:

---

Time_Diff = If(IsEmpty(Modification_Time)=1,Time("","",""),Modification_Time-Creation_Time)

This assumes your creation and modification times are on the same day. If they are not, then you will need 2 new date fields, a Creation_Date and a Modification_Date. Your calculation then becomes.

code:

---

Time_Diff = If(IsEmpty(Modification_Time)=1,Time("","",""),(Modification_Date - Creation_Date)*86400 + (Modification_Time-Creation_Time))

Russ Baker

[hoops]

Russ

Thanks for that, I can create the calulation you suggested, my field is Time Diff and I have set it as time and put the calculation in the > Options > Validation > Validation By Calculation.

One record totalled the difference but on further modification did not update...also created another record and the diff remained at 0.00 even after 5mins.

[danjacoby]

Are you trying to track the amount of time spent on the DB? If so, you can create three global fields, all returning time -- call 'em gStartTime, gEndTime and gTotalTime.

Create an "Open" script that inserts the current time into the gStartTime field. Create a "Close" script that inserts the current time into the gEndTime field and sets the gTotalTime field to the difference (gEndTime - gStartTime) -- and maybe shows the field in large type for, say 10 seconds. Set prefs to run the scripts on opening and closing the DB.

[RussBaker]

You have used the calculation as a field validation.

The Time Diff field should be a calculation field, with the result set as Time, using the calculation shown.

That should do it.

Remember - this calc works on your original fields - one of which is a record of the modification time. So, this is an auto-enter of modification time, then if you don't modify the record, then you will not get a time difference.

Russ

[hoops]

Thanks Russ that works a treat, basically its a daily time sheet tracking the amount of time spent by designers on jobs which is related to a job id db.

They create new record select the job id, do the job and when finished hit a stop button (which is the modification time)..then new record for the next job.

Aslong as they remember to hit the stop button and don't let it run overnight...otherwise the total value will be a negative one

[hoops]

Actually a start/ stop.....stopwatch would be great..the same record could be opened again (days later) started and stopped and the incremental time taken could be accumulated....any pointers?

[RussBaker]

Even though you say its a day-by-day business... I'd still be tempted to use the calculation which uses the date as well as the time to get the final result. As well as avoiding negative numbers, it also covers the possibility of a late-night job which goes over midnight.

It will also allow you to cover more in-depth analysis of your work patterns by allowing you to get summaries of who worked on what days on what jobs etc.

Russ

[RussBaker]

Sounds like some self-relationships coming up here. You could have this relationship, using a component of the Job ID as the key, which would then show a brief history of all other records of the same job, and a sum of the Relationship::time fields would give you the total time spent on that job.

Russ

[hoops]

Agreed.....going with your 2nd calculation!!