Hi List,

Honest...I have searched the archives but could not find an answer!

I am in need of help in a calcualtion that correctly provides for elapsed time when the start/and end time cross midnight, displays results in "number" of minutes, and rounds to the closest minute.

My fields are

Start_Time (time field)

End_Time (time field)

Elapsed_Time (number field)

I am currently using:

Round((End_Time-Start_Time)/60;0)

result as a number

The cross over from morning to afternoon works OK,but I know this calculation is in correct when the start and end time time crosses midnight.

Thanks in advance for any help.

Steve

You have three choices. Use the 24 hour times, i.e. 2400 = Midnight, and sign out the using 2600 for 1:00 AM, 2700 for 2 AM, etc., or include the Start date End date in your calculation, or stop your time at midnight and start start the next day with 0000

Take a look at this Tech Article at FMI site;

Click Here

HTH

Lee

Thanks for the input! I have already printed the tech sheet from FMP and will begin to apply.

Thank you,

Steve

If you make your "Time Fields" Timestamps, you can just subtract them.

End_Time - Start_Time

Will return what you want I think.

Unfortunately I can't do them as time stamps because this is imported data.

However, based on the last suggestion I received this calculation from the FMP tech site works.

Mod(end_time-start_time+86400;86400)

result = number in sconds

to convert to minutes

Mod(end_time-start_time+86400;86400/60

result = number in minutes.

this works if you are going only from one day to the next. If you are bridging more than one day, then you must hav a start date and an end date as well.

((DateEnd - DateStart)*86400) + end_time - start_time

result = number in seconds. Changing to minutes is easy, just divide by 60.

Now...My new question is.....How can I get the result to round up or down to the closest minute!

Sorry to be so needy! But as always this forum is an amazing resource. Thank you for everyones help.

Steve

I ran across this calculation that might help you.

Case ( Time_End ≥ Time_Start, Time_End - Time_Start, Time_End - Time_Start + 86400) / 60 / 60

Lee

[color:red]BTW this is only good if time doesn't exceed 24 hours

Edited November 30, 200520 yr by Guest

Hey all...

I added a new field to my DB for END_DATE

And this formual does not seem to work as advertised (by FMP)...unless I am doing something wrong.

((DateEnd - DateStart)*86400) + end_time - start_time

My fields and current contents:

Date_Start <<8/15/2005>>

Date_end <<8/17/2005>>

Start_time <<6:49am>>

End_time <<11:57am>>

Elapsed time "result" <<173,108.40>>

Formula reads

((Date_end - Date_Start)*86400) + end_time - start_time

result = 173,108.40 seconds = 2,885 min. Not what I was expecting to see.

When I do the simple formula (as referenced in previous post) calc works perfect, so long as I am not measuring more than a 24 hour time span.

I kind of need to figure this one out because we have some jobs (less than 1% though) that stretch across more than one day. This is usually though because the start and end time bridge a weekend of no activity.

What would be the ultimate is to include in the calculation a method to figure a weekend out of the mix...But then what do you do for a 3 day weekend! It sure does get involved.

In an older DB I have created I actually had a formula developed that would remove weekends from a calculation where I was asked to show "days to invoice from order completion" and the finance guy did not like the way monday = 3 days if the job was finished on Friday. It works great, but I can't figure out how to apply it here.

IF(isempty(billing_number::Date_Invoiced);X; Int((Billing_Number::Date_Invoiced-date)/7)*5 + Mod(billing_number::date_invoiced - date;7) - case (dayofweek(billing_number::date_invoiced)

This unfortunately only accounts for 2 day weeknds. My brain got hurt trying to figure out how to tie it into a calander to figure for 3 day weeknds. Finance guy just has to live with that....At least for now.

Steve

You should be experiment with your results. Time and Dates provide a special challenge to get the desired results sometimes.

Try:

Round ( ( ((Date_end - Date_Start)*86400) + End_Time - Start_Time) /60 /60 ; 2)

HTH

Lee

You Rock Lee! This worked after I took out one of the /60's.

Thanks for your help! I owe you a beer. (or beverage of your choice.

Steve