Date to Advance

[drums31]

I have an incident records file that requires times and dates of the following:

- start time

- en-route time

- arrival time

- end time

Each time value is manually entered, and the incident date is auto entered as the creation date (modification allowed).

I am having difficulty with a calculation to apply into a calc field with a resulting date value, the correct DATE to each time (e.g. start-time date, enroute-time date, etc).

Senario Event: As the en-route-time, arrival-time or end-time may advance into the next calendar day or julian date from the start-time (using 24:00 time), the date needs to advance as well.

I've tried to compare each time to the start-time by referencing "< start-time", as the time into a new day would usually have an earlier clock time, but I have not been successful as these times may cross days and have a greater time value.

Additionally however, in the unusual events that the end-time is > 24:00:00 from the start-time (even spanning several days), the date would need to advance as well.

Any thoughts?

Edited July 24, 2005 by Guest

[LaRetta]

Hi drums31, welcome to FM Forums!! Glad you joined us!

Since you are using vs. 7, you might consider just using timestamps instead. They combine the date and time so FM will do the heavy lifting for you. For instance:

start-time: 07/24/2005 9:47 AM

End Time: 7/26/2005 10:00 AM

Calc field (result of Time) of end time - start time would show: 48:13:00

Clicking each time button would just be: Get(CurrentTimeStamp). I think it would be easier than trying to piece it back together and wrap across dates. Your start-time field could even include using your Incident date on it with Auto-Enter (Replace) calculation but you probably won't need to. Regardless, if the fields themselves are timestamps they will track properly. You can format those fields to NOT show dates at all (select each field on the layout) by selecting Date Format > Custom and selecting (None) on each date portion (through Field Format). Your Users will only see the times and won't even know you're sneaking the date in also.

And instead of using a calc field for the result, you can also use standard time field with Auto-Enter (uncheck 'Do Not Replace Existing Value') if you wish.

ps. Since you don't need a Custom Function for this, I'll be moving it to Define Fields in a few days (or after you've read it; whichever comes first).

LaRetta

[Fenton]

You say that you "require the times and dates of the following," which includes "end time." That implies that you also require "end date." Then you say "the date would need to advance." This is a contradiction. I don't think there is any way to advance the date automatically when an event can span "several days." How could it possibly know unless you enter either the end date or the elapsed time?

If you enter the end date it's not hard to figure the elapsed time (result Time)

86400*(DateEnd - DateStart) + TimeEnd - TimeStart

Another:

Timestamp ( DateEnd ; TimeEnd ) - Timestamp ( DateStart ; TimeStart )

[drums31]

Hey, thanks LaRetta and Fenton for your posts. I should probably use my name instead of a screen name. I was a subscriber to this forum years ago. As you can tell by my bio list, I'm pretty busy.

LaRetta,

Unfortunately, the use of this file will not support a time or date stamp, as the actual entry time of incidents may not during the incident. I like the Result of Time calc though, I will look into that-- although, I have formulas for all elapse times already in place.

Fenton,

Yes, The file needs an "end date". I'm all set with elapse times. It seems as though somewhere I need to reference a julian date or "day of year". I have already created a Julian date field, which I think should be the result + the hour differencial from an if statement. I'm equally thinking, i may have to create some additional calc fields to reference beyond a 24:00 field.

My heads swirling with concepts, but I just cannot nail it.

Hoping for some light!

Edited July 24, 2005 by Guest

[Fenton]

It seems simple enough to me. Human beings enter the times and dates. The computer calculates the time elapsed. Either of the calculations I posted supports multiple days. In the 1st calculation, multiplying by 86400 is just converting the number of days to seconds, which is how FileMaker stores time. There's no need for an If condition.

The 2nd calculation just converts the manually entered date and time to a Timestamp. They do not manually enter a timestamp, so there's no question of supporting it for data entry. It's just a convenient way to subtract.

[drums31]

The date fields often get overlooked due to the incident volume. I really need to offer a transparent entry. I will try the seconds conversion.

Calc 2 looks cool too.

Thanks