Add 1 hour to a time field

[yogert909]

Hi,

I'm going crazy trying to write a simple script to add one hour to a time field. Actually, it works the way it should if I use a24 hour time but if I want to see 11:03:13 PM, the system breaks down.

I tried converting 12 hour time to 24 hr time for computing and the back to 12 hr but when I go to 12 hr it drops zeros like this: 11:3:13 PM. This happens when I try to add the "AM" or "PM at the end of the calculated result. Ideally, I'd like to drop the seconds so it reads 11:03 PM. Then I'll run the script and it'll say 12:03 AM, 1:03 AM, and so on...

I'm using the following:

Insert Calculated Result [select, "Scrach Time" , "Hour(Scratch Time) - 12 & ":" & Minute(Scratch Time) & " PM""]

I'd really appreciate any help stopping this madness.

Thanks

[Helpful Harry]

Ummm ... you can simply add 1 hour to a Time field, but it has to be defined in seconds,

ie. SetField [MyTime, MyTime + 3600]

FileMaker itself takes care of am / pm (although not date changes!) and you can use the Format > Time menu option to set the field to display however you want.

[yogert909]

Well, don't I feel stupid! Thank you so much

Works like a charm.

[CobaltSky]

Helpful Harry said:

...takes care of am / pm (although not date changes!)...

If you want to take care of date changes ( ie calculate an accurate time when the result of the adjustment is after midnight, you can use:

Set Field ["MyTime", "Mod(MyTime + 3600, 86400)"]

[Helpful Harry]

I don't see why that's necessary. FileMaker itself will adjust for going past the am / pm barriers, but to also adjust a date when going into a new day requires a bit more scripting, and of course a Date field.

ie. very roughly If MyTime > "12:00am" then SetField(MyDate, MyDate + 1)

[CobaltSky]

Hi Harry,

You'll find that if you run a script that sets a field (eg 'TimeField') that has 11:44pm in it, to:

TimeField + 3600

- the value in the field will be 24:44:00 - and the time value will continue incrementing upwards with each further call of the script, until the value in the field reaches 32766:59:59 (a value which represents 117,961,199 seconds - a span of about four years). After that, FileMaker will no longer recognise it as a valid measurement of time (and the field will show a '?' to signify an invalid/unrecognized value).

It is only if you apply a *custom* time format to the field on a particular layout, that FileMaker will then re-interpret the value and display it as an 'intelligible' time (eg 12:44 AM). But this is only a display device ( a 'mask'). The value stored will remain as 24:44:00 and that is what users who click into the field will see - whereupon they will be apt to become confused.

The revised formula I suggested above will convert time values which exceed 24 hours into *stored* values that most humans will recognise as a time of the day (or night as the case may be).

The matter of incrementing a date field (if any) to reflect the advance of the calendar associated with such a change is a separate question - however FWIW, the script formula you've suggested would fail if the time value were incremented (in one or more stages) by more than a further 24 hrs. To deal with a date simultaneously, I would suggest that as the time is incremented, the date be re-evaluated with a formula such as:

DateField + Int((TimeField + ExtraTime) / 86400)

- which will work for increments of 3600 (1 hour) but also for much larger values. However this is probably moot, as it's rather beyond the scope of yogert909's original question.

[Helpful Harry]

You're right. I never noticed that since all my times are always formatted / displayed as 12-hour (which works) and not 24-hour.

Thinking about it, FileMaker probably does that to allow you to be able to do Stop-watch type fields, rather than proper clock fields.

Thanks for the correction.