Formatting Elapsed Time

[Jarvis]

I have a calculation field that subtracts one timestamp from another to capture elapsed time. The formula for this field is:

(STOP TIME - START TIME)/60. The result shows how many minutes have elapsed from start to stop.

I would like this field to display as minutes if the result is less than 60 minutes and hours & minutes if the result is more than 6o minutes.

For example:

45 minutes = 45 minutes

90 minutes = 1hr-30 minutes

Any ideas how to accomplish this?

Thanks,

Jarvis

[Ocean West]

One method would be use of the Choose function... albeit a long one.

Choose ( (Stop Time - Start Time ) / 60 ;

"zero minutes" ;

"one minute" ;

"two minutes" ;

---- etc ---

"45 minutes" ;

---- etc ---

"1hr-30 minutes" ;

---- etc ---

)

[Lee Smith]

Take a look at my Custom Function

Here

[Jarvis]

Stephen,

I was hoping for something a little more succinct, and it looks like Lee has it!

Lee,

That is really cool.

I now know that I am 53 years 10 months 4 days old.

I never figured I'd make it to 25.

I think I will stare at this for a while to see how I would make it read hours & minutes.

Jarvis

[LaRetta]

That's great, Lee, but we need it rounded to quarter hours as well. And Choose() just doesn't fit well here ... we might need to extend for every minute (or even quarter hour) for how long? Days possibly? You might consider something like this, calculation result is text:

Let (

[

minutes = Div ( STOP TIME - START TIME ; 60 ) ;

hours = Div ( minutes ; 60 ) ;

quarter = Floor ( Mod (minutes ; 60 ) / 15 ) * 15

] ;

[color:green]If ( hours ; hours & " hour" & If ( hours > 1 ; "s") [color:green]) & If ( hours ; "," ) & [color:green]If ( quarter ; quarter & " minute" & If ( quarter > 1 ; "s" ) [color:green])

)

I also mistrust ANY date calculation which uses 365.25 or ANY set formula for number of days in year. It will break.

UPDATE: Corrected calc - I didn't know if you wanted to round up or down so I went down instead and I added test to keep from displaying minutes if 0 (green).

[LaRetta]

Geez. This works as well. Using time itself is sweet. This rounds up:

Let (

[

t = Time ( 0 ; 0 ; STOP TIME - START TIME ) ;

hours = Hour ( t ) ;

minutes = Ceiling ( Minute ( t ) / 15 ) * 15

] ;

[color:green]If ( hours ; hours + ( minutes = 60 ) & " hour" & If ( hours > 1 ; "s") [color:green]) & If (hours ; ", " ) & [color:green]If ( minutes ≠ 60 ; minutes & " minute" & If ( minutes > 1 ; "s" ) [color:green])

)

... picked up the concept of using time to convert automatically from Comment ...

UPDATE: Adjusted calc to drop minutes (but add 1 to hour) if minutes via rounding up by 15 is equal to 60 (green).

[Lee Smith]

LaRetta has provided a couple of calculations that will probably be what you need, although I don't see where you asked for the time to round, but then my eyes are tired, as it has been a long day, so I might have missed it.

My mistake on the Custom Function, you are right that it doesn't take it down to hours, minutes and seconds.

However, Geoff Wells provided one that does, and posted it on Brian Dunnings site. It is called ElapsedTime ( TStampBegin; TStampEnd; Format )

and you can copy it from

here

I just tested it and seems to work pretty good. Geoff has put some flexibility to it, allowing you to choose which format you want. I think "String" is the one you will one for this application.

HTH

Lee

[LaRetta]

You're right, Lee! Rounding to 15 minutes may not be needed. Thanks! That's the problem with one small example. But I'm sure Tim can get it now with all the options we've provided. But a custom function certainly isn't needed in any case so my examples will help those without FMA. Nor is it necessary to break the times up as that CF shows ... it's quite a bit simpler just using time() itself ...

Again ... it wasn't indicated whether to round seconds so I dropped them. But this sure appears easier than a CF. The ONLY complication is formatting the result and NOT on applying the math - time does that all by itself; just like with dates:

Let ( [

t = Time ( 0 ; 0 ; STOP TIME - START TIME ) ;

hours = Hour ( t ) ;

minutes = Minute ( t )

] ;

If ( hours ; hours & " hour" & If ( hours > 1 ; "s" ) & ", " ) & If ( minutes ; minutes & " minute" & If ( minutes > 1 ; "s" ) )

)

[Søren Dyhr]

Let ( [

t = Time ( 0 ; 0 ; STOP TIME - START TIME ) ;

hours = Hour ( t ) ;

minutes = Minute ( t )

] ;

If ( hours ; hours & " hour" & If ( hours > 1 ; "s" ) & ", " ) & If ( minutes ; minutes & " minute" & If ( minutes > 1 ; "s" ) )

)

It escapes me why you need the Time( in this calc ...must be some kind of legacy from something Ugo and Comment have been doing.

However if you assume you could use the same principle with dates, does it go way wrong because everything beyond 60 days will include february in year 1 which isn't a leap year - and while we're at it will february always be shorter than the avarage - meaning the measure of month between two events always are a little iffy... if the offset isn't considered.

My point is that if the fields are formatted correctly as time will there be integers behind, and the hour( and the minute( knows how to deal with numberbase conversion and the remainders.

Refresh my memory why this step is necessary?

--sd

[Jarvis]

Since you guys are doing all the heavy lifting,

I guess I can go back to building cabinets.

Thanks a lot!

Jarvis

[LaRetta]

Hi Soren, you are right in that Time() wasn't needed here. I learned the theory of using time in a post by Comment...

Filemaker can do this for you. A calculation field (result is time) = Time (0 ; minutes ; 0 ) will convert any number of minutes into the overall time. You can then format this on the layout to display as hh:mm.

I was applying same theory in a text calc instead of field-level format and now see it was overkill but the principle is still the same (if it's broken into its finite first).

...if you assume you could use the same principle with dates, does it go way wrong because everything beyond 60 days will include february in year 1 which isn't a leap year...

It does NOT break if it is first converted to its finite. I wasn't clear ... days are the finite to DATES just as seconds are the finite to times and timestamps. Date - numberOfDays will ALWAYS correctly translate backwards through dates (even leap years) just as TimeStamp - numberOfSeconds will always translate backwards through time (even leap years). FM correctly performs the math. For instance:

A TimeStamp of 2/27/200[color:green]4 10:00:24 AM + 433281 ([color:green]leap year) will correctly adjust the date and produce timestamp result of 3/[color:red]3/2004 10:21:45 AM. But 2/27/200[color:green]5 10:00:24 AM + 433281 ([color:green]not leap year) will correctly produce 3/[color:red]4/2005 10:21:45 AM.

Yes indeed, FM can handle the math. That's why I'm amazed to see all the breaking apart (such as the CF string) of what FM so nicely puts together on its own xor hard-coding 365.25 (birthday CF) which breaks on many dates. Days were not asked for in this calculation so I didn't kick to days. And there should be protection if either field is blank. To include days (and correct my earlier blunder), the translation would be:

If ( START TIME and STOP TIME ;

Let (

[

t = STOP TIME - START TIME ;

days = GetAsDate(STOP TIME) - GetAsDate ( START TIME ) - (GetAsTime ( START TIME) > GetAsTime ( STOP TIME ) ) ;

hours = Hour ( t ) - days * 24 ;

minutes = Minute ( t )

] ;

If ( days ; days & " day" & If ( days > 1 ; "s" ) & ", " ) &

If ( hours ; hours & " hour" & If ( hours > 1 ; "s" ) & ", " ) &

If ( minutes ; minutes & " minute" & If ( minutes > 1 ; "s" ) )

)

)

My point is that if the fields are formatted correctly as time will there be integers behind, and the hour( and the minute( knows how to deal with numberbase conversion and the remainders.

Except the result requires Days, Hours and Minutes broken out into a text concatenation. We can't simply format the calc result as time because of the display need.

LaRetta

[Søren Dyhr]

A TimeStamp of 2/27/2004 10:00:24 AM + 433281

Yes that way it does if you use + but if you like you did in your calc made it look like Date( would a MINUS for the diff of approximately 60 days always deal with 0 for the year, but by it be crossing the same long february each time.

--sd

[LaRetta]

Oh okay, I see your point. No, don't try using Day ( DIFFERENCE ) with dates. That wouldn't make sense (you are right) and I didn't mean it would. In fact, I didn't follow that path either when dates were pulled into the calculation (and my calculation works). Dates and Times/Timestamps are like comparing apples and peaches - they are both sweet but only one is crunchy.

Thanks for clarifying in case others thought that as well. Keep in mind that days weren't asked for here - I only added them as an afterthought after you posted with questions about the dates portion. If days needed to be included, I might have approached it differently from the start.

:wink2: