Cumulative Time Calculation Across Multiple Daily Records

[scchang]

I have a database of daily records that each contain a start and stop time. I would like to calculate the time elapsed from each day's first record start time to the last record end time. I've played around with calculations, but am having trouble referencing the first and last daily records. Any help appreciated!

[comment]

There are are several ways you could approach this. The simplest one, I think, would be to produce a report where the records are sorted by date. Define a summary field as Minimum of StartTime and another summary field as Maximum of EndTime. Place a calculation field (result is Time) =

GetSummary ( sMaxEndTime ; Date ) - GetSummary ( sMinStartTime ; Date )

in a sub-summary (when sorted by Date) part on your report layout. 

[scchang]

Thank you. I created and named the MinStartTime and MaxEndTime summary fields. For a single day's records, each returns the correct first start time and last end time. I can calculate the total hours from this.

I created the calculation field as you described. I'm not sure of the significance of the "s" in each time filed though so I entered the name as e.g. "MinStartTime" as that is what I named it. This calculation field does not give a result with either a single day of records or with multiple days of records. I think part of the problem is that with a group of records spanning multiple days, the min and max times are the minimum and maximum for the entire range of days.

I tried to create a running total fo the total daily hours, but this doesn't work for multiple days because the min and max times are for the entire date range.

What am I missing?

[comment]

I am afraid you have lost me somewhere along the way. I thought you had records with fields for:

• Date
• StartTime
• EndTime

And wanted to see the earliest start time and the latest end time of each day. But now I see you have two date fields, so I must have been wrong.

[scchang]

You're correct. Sorry to confuse the matter. The two dates are essentially the same. Very rarely, the end time occurs the following day. I don't want to consider that case at all here.

However, I do want to know the cumulative time for multiple days. For example, specify a range of days and calculate the total number of hours for those days. Since I can calculate the total hours per day thanks to your help, is there a simple way to do a running total?

 

[comment]

38 minutes ago, scchang said:

is there a simple way to do a running total?

It is a bit complicated, because the sub-summary values are produced ad hoc and do not really exist at the data level where they could be totaled. If you want to remain in the context of a report, you could solve this by adding a couple of fields - see the attached demo. Note that this works on the current found set, so if you want specify a range of days, you need to perform a find.

TotalOfSubsummary.fmp12

[scchang]

Thank you! I think I can figure it out from the demo you sent. I very much appreciate your kind help. I'll work on it and let you know. 

[scchang]

The calculation is working well. Thank you for all your help!😁