9 Replies Latest reply on Feb 29, 2016 1:55 PM by user19752

# Date and Time Calculation Question

Hi

I am trying to create calendar entries for various events.

Some events cross midnight boundaries and some don't, depending on duration and starting times.

With a known Starting  Date (StartDate), starting Time of Day (StartToD) and Duration in hours (DurationHours), how can I calculate the End Date (EndDate) and ending Time of Day (EndToD)?

eg:  Start date and time = 2016-01-01 at 15:00hrs.

if the event duration is 20 hours, what is the end date and what is the ending time of day?

and if the duration is 3 hours, what is the end date and what is the ending time of day?

Peter.

• ###### 1. Re: Date and Time Calculation Question

Hi Peter,

have a look at time and dates functions. You can for example subtract and add dates of times.For your application, you should use a TimeStamp which is a date and a time. Now regarding the duration of an event, do the 20 hours include night(s) ? If not, you should then take into account part of the day where the event is not taking place.

On way to play around with functions is the Data Viewer in File Maker Pro advanced, for example:

Timestamp() creates the time stamp for the start of the event. To that we add the number of seconds in 20 hours (a timestamp is in seconds) and we get a new timestamp 20 hours later.

• ###### 2. Re: Date and Time Calculation Question

EndToD = let (

l = time(DurationHours;0;0);

time(mod(hour( StartToD + l ) ; 24) ; minute(StartToD); seconds(StartToD))

)

EndDate = StartDate + (EndToD < StartToD)

• ###### 3. Re: Date and Time Calculation Question

Yes, timestamp can do the math. You can also have fields that auto-enter date and time (mostly for display and other options) into two fields based on the timestamp.

I have date_start, time_start, date_end, time_end in an "event" database I manage for my own usage. All of these can be used to do "the math"! The timestamp just combines both and works well, too.

beverly

• ###### 4. Re: Date and Time Calculation Question

In addition, you can use Time(20;0;0) function at duration.

OP already have these as proper type of fields, so

Timestamp ( StartDate ; StartToD ) + Time ( DurationHours ; 0 ; 0 )

get result as timestamp.

If want indivisual fields for date and time, there are functions.

GetAsDate ( timestamp )

GetAsTime ( timestamp )

• ###### 5. Re: Date and Time Calculation Question

Thanks for this ... It seems to be the closest to what I am trying to do, but I can't get it to work ... my skills are not up to that level to be able to debug.

should EndDate = StartDate + (EndToD < StartToD) have a plus sign?  though that doesn't work for me either.

I've attached some screenshots of what I've tried.

Thanks.

Peter

• ###### 6. Re: Date and Time Calculation Question

There are many ways, but as planteg said, in this problem you can use timestamp to make things easy.

Set Variable [ \$endTimestamp ; Value:Timestamp ( Globals::gStartDate ; Globals::gStartToD ) + Time ( Globals::gDuration ; 0 ; 0 ) ]

Set Field [ Globals::gEndToD; Value:\$endTimestamp ]

Set Field [ Globals::gEndDate; Value:\$endTimestamp ]

This calculate correct value for any of duration, not only crossing one midnight.

• ###### 7. Re: Date and Time Calculation Question

Thanks to everyone for their help. Timestamps are the way to go for this question.

here are the details which worked for me:

Script:

Fields: (remember to set the duration as a number, not as a time. Decimals work too (eg 12.5))

My testing layout:

Thanks again to everyone.

Peter.

• ###### 8. Re: Date and Time Calculation Question

Thanks .. needed to add GetAsDate and GetAsTime.

worked perfectly.

• ###### 9. Re: Date and Time Calculation Question

"Set Field" converts the result to its field type, then usually can omit GetAs... function. I tested it before post. But using them always may be safe and good to readability for someones (I like shorter version always)