Subtracting Dates (Again)

[javabandit]

I've searched and found many topics on this forum, but I didn't find any that address my question yet. My apologies if this has been answered many times before.

I'm using two timestamp fields to "start" and "end" a job. I then calculate the time elapsed as such:

start (Timestamp field set by a button & script)

end (same)

c_time_elapsed = If ( IsEmpty ( end ) ; Get ( CurrentTime ) ; end - start )

Calculation result is Time

My problem:

Sometimes c_time_elapsed shows up with an AM or PM attached to it. And, other times it shows up with a 12 hour offset. I don't understand that, but it throws off the subtraction.

For instance: (this is in my db right now)

start 7/6/2010 2:40 PM

end 7/6/2010 3:43:01 PM

c_time_elapsed = 1:03:01 AM

There should be no AM or PM associated with a time difference, it's just a length of time.

Here's another example. I just did the following:

start 7/6/2010 4:45:35 PM

end 7/6/2010 4:45:46 PM

c_time_elapsed = 12:00:11

I then went in and edited the "end" field, by deleting the 6 in the date, and then re-entering the 6 and committing the record. When I did that, it recalculated and my value changed to this:

c_time_elapsed = 00:00:11

So, that's what I wanted, but why didn't I get that the first time?

A stripped down version of my file is attached. Any help is appreciated.

REPAIR_LOG_FORUM.zip

[Raybaudi]

Hi

While in layout mode, select those time fields and Format >> Time >> Leave data formatted as entered.

Also change those calculations for time_start and time_end to GetAsTime ( YourTimeStampField ).

Same for start_date [ GetAsDate ( YourTimeStampField ) ]

[JesseSFR]

I'm not entirely sure that I understand the issue but it appears to me that the above calculation would return the current time with the AM/PM only when the end_time is empty.

c_time_elapsed = If ( IsEmpty ( end ) ; Get ( CurrentTime ) ; end - start )

You might want to change this to be

c_time_elapsed = If ( IsEmpty ( end ) ; Get ( CurrentTime ) - start ; end - start )

That could solve your problem

[Matthew F]

When you subtract two timestamps the resulting value is the interval between the two, in seconds. You should set the data type in your calculation field to 'Number' instead of 'Time'. Do additional calculations on this value to get hours or minutes if needed.

It is a mistake to indicate 'Time' as the data type for this calculation. In doing so you are asking Filemaker to interpret the result as a time of day. Any numerical value will be treated as the number of seconds after midnight. Thus, the value of 600 would be displayed as 00:10:00 (10 minutes after midnight).

[Raybaudi]

It is a mistake to indicate 'Time' as the data type for this calculation. In doing so you are asking Filemaker to interpret the result as a time of day.

I don't think so.

[LaRetta]

Take a look at a stopwatch. 10 minutes is 00:10:00. I agree with Daniele.

[javabandit]

Okay, this is helping.

First, I must have been tired, because I completely missed my mistake in this:

c_time_elapsed = If ( IsEmpty ( end ) ; Get ( CurrentTime ) - start ; end - start )

Thank you JesseSFR for pointing that out.

I also took Daniele's suggestions and edited my times and dates to use GetAsTime(Timestamp) and GetAsDate(Timestamp). That's a more simple way than what I was doing. Thank you.

I am still having two issues:

First, I am having trouble with the Data Formatting. As Daniele suggested, I selected the fields and set Format >> Time >> Leave data formatted as entered. I then exit the layout, and go back to it, and they revert back to Format >> Number >> Fixed number of decimals 2. This is FM Pro Advanced 11, and I make the change in the Inspector, but it's just not sticking.

Second, I use a script called from a button to allow a "beginning of shift" start. The script is "TEST__start_beginning_of_shift". Testing it this morning, the beginning of shift should be 8 o'clock. When I invoke the script with the "8 AM" start button, I expect the the start time in the portal to display "8:00:00". Instead, it's displaying "8:00 AM", which is inconsistent. What's going on here?

I've attached a revised version of my file.

Thanks in advance for your help.

REPAIR_LOG_FORUM.zip

[bruceR]

Your change IS sticking. Click the clock icon to see. Also, you're giving conflicting statements. If you want the result to appear as 8:00:00, that's what you will need to specify, NOT "as entered." No decimal specifier; hhmmss; 24 hour.

[javabandit]

Okay Bruce... I've got it now.

I added a bunch of copies of the same field to my layout and tested the different options for formatting the time. Now I see how it works.

I was expecting the Inspector to act differently than it does. When I clicked on my time fields, I was thinking that the Inspector would switch over to the clock icon and display the settings I had last chosen. But now I see that the Number format applies at the same time as the time format, so it makes sense.

Thanks for the help everyone!

[Matthew F]

Take a look at a stopwatch. 10 minutes is 00:10:00. I agree with Daniele.

Well yes, you both are right, as long as you are talking about an interval of less than 24 hours. On the other hand, if you have a time interval of 24hrs and 10 minutes. The result will be 00:10 or 12:10 AM, rather than 24:10 (hr:min). The time data type acts like a stopwatch that rolls over to 0 when it reaches 24 hours.

In javabandit's case it works because the workers will not be punching their time cards for longer than 24 hour intervals.

[comment]

if you have a time interval of 24hrs and 10 minutes. The result will be 00:10 or 12:10 AM, rather than 24:10 (hr:min).

Will it?

TimeDiff.zip

[Raybaudi]

... as long as you are talking about an interval of less than 24 hours.

Whichever interval from two timestamps returns the number of seconds between them; having a time result that interval will be displayed in the form:

hh:mm:ss

with hh going even over the 24 hours.

[Matthew F]

Thanks for straightening me out Comment. I'm wondering now why I observed different behavior when I created the same calculation. Can't rule out psychosis at this point.

[comment]

Matter of formatting, perhaps? You WILL observe the described behavior with 12-hour formats.

[javabandit]

In javabandit's case it works because the workers will not be punching their time cards for longer than 24 hour intervals.

Not exactly... One problem that we have is people who forget to "punch out" when they are done with something. So, I do need it to continue to show the true interval (end - start as timestamps) so that it's easy for the shop manager to identify ridiculous entries and correct them.

With all the suggestions above, however, I do have things working and displaying the way I would like them now. Thanks to all.