Total Hours Calculation ?

[sicSRT8]

Is it possible to eliminate AM and PM from a Calculation Field set as a Time Output. Im trying to calculate total hours from a start field and an end field and get the number i'm looking for but has an AM showing and want to eliminate that. Both start and end fields are set as time fields.

[David Jondreau]

Why are you using a Time field for the result? You are trying to return a Number.

[LaRetta]

Using a time field is okay. Returning a number would produce seconds elapsed. But if you don't enter the time as 1) military time or 2) enter an AM/PM, FM will not know which to use. For display purposes, you can control whether the AM/PM shows at the layout level (see field formatting time).

The result of your calculation can still also be time, ie, two time fields Start and End would be:

End - Start

If End = 12:15 PM and Start = 11:45 AM

Then a time field calculation result would produce 0:30:00 which is quite fine, indicating 30 minutes. Much depends upon what you want and why you want it. Could this time span ever go over midnight? :wink2:

Edited March 12, 2009 by Guest

[sicSRT8]

I need total hours 7:00 is fine but 7:00am isnt..

[David Jondreau]

You're confusing hours as something you count as hours as an element of time. A calc field with a Time result is inappropriate here (sorry LaRetta, it's true). It's a Number result you want.

Start with this formula:

(Time2-Time1)/60/60

Modify that with Round(), or Truncate() depending on you need for precision (round up or down, etc).

7:00 doesn't mean 7 hours. It means 7 o'clock.

[comment]

I don't see why you couldn't use a Time result to show the difference between two times or timestamps - as long as the result is formatted properly.

7:00 doesn't mean 7 hours. It means 7 o'clock.

And what does 277:46:40 mean?

[sicSRT8]

That works.

[LaRetta]

A calc field with a Time result is inappropriate here (sorry LaRetta, it's true).

Nah, DJ, a time result is quite appropriate. Have you tried the example I listed? Why monkey the results by /60 /60 when FileMaker does it for you simply by formatting as time? Simply change the time format (in field behavior) to eliminate the AM PM. 7 hours is 7:00:00. 30 minutes, 15 seconds is 0:30:15. And it's true in more places than FileMaker.

I guess I have a lazy streak (and I'm no longer ashamed of it either). Laziness = efficiency. :king:

Edited March 12, 2009 by Guest

[David Jondreau]

And what does 277:46:40 mean?

It means nothing intuitively to me. 277 hours means something to me though.

What does it mean to you?

[David Jondreau]

And when the difference between two times is not precisely an hour and you need to apply Round() or another number function? The OP said he wants the number of hours, not the number of seconds. A Time result still gives you the number of seconds.

[LaRetta]

The OP said he wants the number of hours, not the number of seconds. A Time result still gives you the number of seconds.

No. The OP said, "Is it possible to eliminate AM and PM from a Calculation Field set as a Time Output.. YOu don't need to show the AM PM! You can drop it at field format or just set to military (24-hour) time!! Have you played with time formats?

We were not asked to convert that time field nor would I suggest it. The field is far more versatile left as time; one time field can be formatted many ways ... one number field can't be formatted in ANY way as time (you would need to create additional calculations to convert it again if you then want hours and minutes, or hours minutes and seconds! Most variations of time are for display purposes only.

And when the difference between two times is not precisely an hour and you need to apply Round() or another number function?

ROFLMAO! Why? Simply format as hh only (24-hour) and you get (surprise) rounded hours without seconds. Maybe in another location you want hours and minutes. No problem ... format as hhmm. The same is true of timestamps; it is best to leave them in their natural state when possible.

[LaRetta]

It means nothing intuitively to me. 277 hours means something to me though.

What does it mean to you?

Hmmmmm, pretend you have a stop watch and you click start. Then later, you click stop.

The watch shows: 277:46:40. Now what does it mean to you? I spent many soggy hours as soccer coach with a stopwatch in my hand. Time is commonly shown in this manner to represent time passing.

[comment]

What does it mean to you?

Why, it means 277 hours, 46 minutes and 40 seconds, of course. A standard time notation doesn't necessarily mean it's the time on a clock. Haven't you ever seen a countdown displaying remaining time as hh:mm:ss?

Or here, for example:

http://www.marathonguide.com/results/browse.cfm?MIDD=488090215

Do you think 2:51:31 means the winner finished at around 3 o'clock in the morning?

And when the difference between two times is not precisely an hour and you need to apply Round() or another number function?

Why couldn't you round time to the nearest hour and still display it as time - if that's what you prefer? You often see dollar amounts rounded to the nearest dollar displayed as $1,234.00 - how is that different?

[comment]

Simply format as hh only (24-hour) and you get (surprise) rounded hours without seconds.

Ahm, this is where we part, I'm afraid. It's not rounded, but truncated. You have to do your own rounding.

[LaRetta]

I misspoke, thanks for the catch. Of course it is truncated; it only displays the hours. Rounding would be an individual call, ie, we will pay salary to 1/4 hour, rounding up or down is the business decision. I appreciate being held to accurate statements and I was not accurate on this. Thank you for calling me out; just as I appreciate DJ for questioning. This is the only way to really learn and I envision it similar to those lucky enough to go to college ... staying up all night discussing things and bouncing ideas off of each other. I adore it. :wink2:

[David Jondreau]

L & c,

I'm having trouble replying to both of your comments separately, as they seem to overlap in some ways.

L, You must have some tough soccer players, running for 11 1/2 days! My stopwatch (I coach high school rugby players) stops running at 24 hours. Pretending to have to let that clock run for over 10 days, well, that isn't intuitive to me.

It is unusual, unless you work for NASA or are an endurance athlete, to present times in a standard time format of over 3 hours. In both those cases, the number of minutes and seconds actually matter. In most other cases, it doesn't.

I read the OP and see this:

"Im trying to calculate total hours from a start field and an end field "

That's a number result, a number of hours.

We're talking about displaying a figure as desired and storing it.

Your solutions, to store it as a Time result and display it depending on the needs seems inefficient to me.

I see little benefit in storing a difference between two times as an exact time. Most often humans are interested in, at a minimum, quarter hours, usually for billing purposes. The OP, as above, said hours, so I must assume he/she will round up or down or truncate to achieve those hours.

C: "Why couldn't you round time to the nearest hour and still display it as time"

Exactly. how can you round time to the nearest hour without converting it to a Number of hours first. Apply Round() to a Time field. You get a perfectly rounded number of seconds. You still have to apply the /60/60 calc to get a useful number of hours.

A Time field stores time. A Number field stores a number (of hours). That's my story and I'm sticking to it. :

Edited March 13, 2009 by Guest

[LaRetta]

My stopwatch (I coach high school rugby players) stops running at 24 hours. Pretending to have to let that clock run for over 10 days, well, that isn't intuitive to me... That's my story and I'm sticking to it. :

Ha h ha!! Obviously!!! In my book, d, this is called a thinking error. In anyone's book, you can always find justification for ANYTHING if you try hard enough. And you did. However, justification does not equal truth. But you can win. Feel better? :

[comment]

Actually, OP said "I … get the number i'm looking for but has an AM showing and want to eliminate that". But I think we are beyond that by now.

In terms of information, "7.25 hours" and "7:15" are exactly the same. So the only remaining question is how to display that information. Personally, I find "7:15" more convenient, esp. when it's preceded by:

Start: 9:20

End: 16:40

Duration:

I feel that putting "7.25" in this context is confusing, and very likely to be misinterpreted as "7 hours, 25 minutes".

how can you round time to the nearest hour without converting it to a Number of hours first.

Easily:

Round ( t / 36 ; -2 ) * 36

[sicSRT8]

Wow that was interesting... I go home leaving a question and come back to find a whole mess of answers. Now this is a Forum! :

PS: Feel free to do this for all my questions

[rigman]

Wow that was interesting... I go home leaving a question and come back to find a whole mess of answers. Now this is a Forum! :

PS: Feel free to do this for all my questions

So what did you do eventually??

Did you ever considered eliminating the AM PM "visually"? Just hiding it by placing an object over it, like a filled square for example... I know its cheap and easy :

But it could work for you depending on what you want to do.

[sicSRT8]

it was for reporting... the "Band aid Method" would not have worked.

[LaRetta]

Did you ever considered eliminating the AM PM "visually"?

This is precisely what I suggested at the beginning; remove the AM PM from the DISPLAY of the result at the layout level (format of time).

It does not seem that many people actually USE these layout-level formats for time, date and number. It is a shame. Eliminating the AM PM this way would work quite well for reporting!!!

[comment]

It's also much easier than trying to place a "patch" precisely over a field. Not to mention that merely hiding the AM/PM part would show incorrect results:

- a total of 45 minutes would show as 12 hours and 45 minutes

- a total of 13 hours would show as 1 hour

and so on.

[LaRetta]

Not to mention that merely hiding the AM/PM part would show incorrect results

Hiding the AM/PM or formatting at layout level to 12-hour format (but skipping the AM/PM) BOTH would produce incorrect results. Nice catch, Michael. Use only 24-hour layout level format on this type of time calculation.