Jump to content

Cumulative Time Calculation Across Multiple Daily Records


This topic is 1745 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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. 

  • Like 1
Link to comment
Share on other sites

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?

Screen Shot 2019-05-23 at 3.11.38 PM.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 4 weeks later...

This topic is 1745 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.