Calculating times

[Baylah]

Hi,

It's been a while since I posted. New job, new city...holy cow....but I digress.

Anyway, I need some help with what I thought was a simple calculation.

I need to retrun the number of minutes elapsed between two times, with the curve thrown in that sometimes the times cross over AM/PM and my source times are entered that way as opposed to "military time"

Start time = 11:47:33 AM

End time = 12:13:37 PM

Result = Minutes elapsed. If the result is more than an hour I still want it to return only total minutes.

Can anyone help me with this one?

Thanks,

Steve Freeman

[VICH]

this link may help, with some adjusting of the caclulation..

http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=4251&p_created=1115172819&p_sid=6REijYJh&p_lva=&p_sp=cF9zcmNoPTEmcF9zb3J0X2J5PSZwX2dyaWRzb3J0PSZwX3Jvd19jbnQ9MjEwOSZwX3Byb2RzPTEmcF9jYXRzPSZwX3B2PTEuMTsyLnUwJnBfY3Y9JnBfcGFnZT0xJnBfc2VhcmNoX3RleHQ9ZGF0ZSBhbmQgdGltZSBpbiB0aGUgc2FtZSBmaWVsZA**&p_li=&p_topview=1

Edited September 9, 2005 by Guest

[Baylah]

Thanks for the reply, that should be very useful. One thing I don't see though that would be very useful, is there a way to round the result up to the next closest minute?

thanks,

Steve

[VICH]

that i actually don't know the application where i was using that formula had to be exact but it sounds like it should be possible

also you might be able to adjust the caculation to return the result in just minutes...i'm sorry but at the moment i don't have time to work it out, but i do have time to point you in the right direction

[Baylah]

thanks,

If I can figure this out properly you can help me really be a STAR in my new job! This DB would impress the hell out of a very "numbers" oriented boss.

Steve

[VICH]

i'm in the same position i am trying to finish mine before next thrusday to present to the owners of the company and the heads of every dept.

[comment]

It doesn't matter how your time is entered or stored (as long as it's correct and valid time entry). The only problem you might have is if you cross midnight. If that is not an issue, you can use:

Round ( ( EndTime - StartTime ) / 60 ; 0 )

to return the number of elapsed minutes.

[VICH]

if he does cross midnight he could replace (endtime-starttime) with the calculation in the link i gave him right?

[comment]

I haven't checked them.

If you cross midnight, there's still the question whether you're allowed to go over 24 hours (such as working a 25 hours shift from 9:00 AM to 10:00 AM on the next day). In such case, the usual test of StartTime < EndTime will return a false positive.

Using time data alone, there is no way to know. The best solution is to use the same formula, but switch to timestamp fields for data entry.

[VICH]

well the formula ((dateend-datestart)*86400)+timeend-timestart) works for anything going past midnight and over 24 hours..i have used it in a timesheet database that i have made

[comment]

Well, if you use date in addition to time, it's the same thing as using timestamp.

[VICH]

ah ok i see now