Elapsed time calculation from two date/time field sets

[bcolburn]

I am trying to calculate the time elapsed based on two set of fields. I have two fields to capture the date and time something was discovered and another two fields in the same record to capture when it was responded to. I am trying to calculate the time elapsed between these two composite points in time but can only find information on calculating minute elapsed between two hour/minute fields or days elapsed between two date fields. Can anyone help me refine my strategy or create a calculation to perform this calculation? And be warned - I'm a relative novice to FM (but learning very quickly!), so be gentle with me!

[bcolburn]

I found a good FM help article online and tried the following calculation, but it only worked for some of my values:

TimeElapsed = abs(((Demographics::Date of Death - Date last seen alive)*86400) + Time of Death - Time last seen alive))

I entered the dates manually and then converted both dates to pull-down calendars to standardize them after 200 records. If some of the values denote the months with two digits (e.g. 02) and some do not, will that matter? It seems the only issue is the time. Sometimes, it simply stops counting after 24:00:00 and sometimes the time is clearly just off. Any ideas?

This is the article I am looking at: (http://help.filemaker.com/app/answers/detail/a_id/4037/kw/calculating%20elapsed%20days)

Also, sometimes the date is the same for both time points, in which case their second method would work better. But is there a way to selectively use one calculation versus another based on those fields being equal? Or shouldn't a more comprehensive formula like method one work for all cases? Not sure where to go from here.

[comment]

I have two fields to capture the date and time something was discovered and another two fields in the same record to capture when it was responded to. I am trying to calculate the time elapsed between these two composite points in time

Try =

Timestamp ( ResponseDate ; ResponseTime ) - Timestamp ( DiscoveryDate ; DiscoveryTime )

Set he result type to Time.

[bcolburn]

That got me even further away from what I want. I think it's just a simple math/conversion error, but I'm not sure. For example when I calculate elapsed time from 1400 (24-hour time) to 1427 on the same day, I get -0:23:20. And when I calculate 7/29/11 (20:00) until 7/20/11 (1507) I get 23:26:40. When I calculate 1900 until 2011 on the same day I get -0:31:40. I'm so lost...

[comment]

Does this work for you?

Duration.zip

[bcolburn]

Totally not fair. Yours works out completely fine. I realized that my time fields were not formatted to be times but were instead just numbers (which should still work fine, but the timestamp contraint probably threw it off). But even when I reformatted those fields and re-entered the data for one record, I still got a bogus answer. Unfortunately, I can't upload the database, as the information is confidential. Is there some other way to get help on this so you can help me figure out what's wrong?

[comment]

I realized that my time fields were not formatted to be times but were instead just numbers (which should still work fine

No, it should NOT "work fine". You cannot enter "1427" into a Number field and expect Filemaker to understand you mean "14 hours and 27 minutes". Filemaker will consider the number 1427 as the equivalent of time 0:23:47 i.e. 1,427 seconds since midnight.

P.S.

I don't know how your time fields are formatted - make sure your entry is 14:27:00 rather than 0:14:27.

Edited September 15, 2011 by comment

[bcolburn]

Right - I understand that. Hence the formatting change. Time fields are formatted as hhm, 24-hour settings.