# Calculate duration between 2 date/time events - weird result

## Recommended Posts

Hello,

Â

Puzzled on why the result is not what I was expecting on date/time calculation. 4 fields: start date, end date, start date, start time...

the following calculation (per FM website)

Â

"((Rental_Date_End - Rental_Date_Start)*86400) + Rental_End_Time - Rental_Start_Time"

Â

should return 90000 (calc. result is configured as number presently) which equates to 86400 seconds in a day + 3600 secondsin an hour.

Â

My result is 86500. (see attached).

Â

Is there a gotcha that I'm overlooking? Also, if there is an issue, once corrected, how would one turn the seconds into a "x days, y hours" type of output.

Â

Thanks as always!

Â

Jack

Â

##### Share on other sites

I cannot reproduce your problem. Using =

```Let ( [
Rental_Date_Start = Date ( 1 ; 22 ; 2015 ) ;
Rental_Date_End = Date ( 1 ; 23 ; 2015 ) ;
Rental_Start_Time = Time  ( 7 ; 0 ; 0 ) ;
Rental_End_Time = Time ( 8 ; 0 ; 0 )
] ;
GetAsNumber (
( Rental_Date_End - Rental_Date_Start ) * 86400 + Rental_End_Time - Rental_Start_Time
)
)```

returns 90000 as expected.

how would one turn the seconds into a "x days, y hours" type of output.

Try something like =

`Div ( ElapsedSeconds ; 86400 ) & " days, " & Mod ( ElapsedSeconds ; 86400 ) / 3600 & " hours"`

##### Share on other sites

Hello Comment!

Thank you for your prompt answers to the questions I posed- your methodology in determining the elapsed seconds does work however how i'm confused-looking at the date field, how would one convert the date inputed into the rental start field, for example, into the proper format i.e. how would a random date entered into the rental start date by the end user, 1/22/15, be converted into this format Date ( 1 ; 22 ; 2015 ).

Another thought- could the start date/time & end date/time be converted to timestamps and then subtract the start date timestamp from the end date timestamp?

Thanks again for your time and guidance.

Jack

##### Share on other sites
how would a random date entered into the rental start date by the end user, 1/22/15, be converted into this format Date ( 1 ; 22 ; 2015 ).

That shouldn't be necessary. If "rental start date" is a Date field, you can enter a date in whatever format your file is using and Filemaker will take care of the rest. I only used the Date()function so that you (or anyone else following) can copy the calculation as is into a Data Viewer or a calculation field and verify the result. If you are getting a different result, something is wrong.

Another thought- could the start date/time & end date/time be converted to timestamps and then subtract the start date timestamp from the end date timestamp?

Sure. That would be even simpler that what you have. Try =

`Timestamp ( Rental_Date_End ; Rental_End_Time) - Timestamp ( Rental_Date_Start ; Rental_Start_Time )`

The result will be 90000 if set to type Number, or 25:00:00 if the type is Time.

##### Share on other sites

Comment,

First problem solved- emabarassed ot say that the cause of my frustration was that time fields I had added were configured as text fields rather than time fields.

My bad for not picking this up this basic error before posting.

Now on to the second issue -the seconds to dd:hh output. I'll report on how I make out with that.

Jack

##### Share on other sites

Sure. That would be even simpler that what you have. Try =

`Timestamp ( Rental_Date_End ; Rental_End_Time) - Timestamp ( Rental_Date_Start ; Rental_Start_Time )`

The result will be 90000 if set to type Number, or 25:00:00 if the type is Time.

Comment,

FYI, your second suggestion also yielded the response I was looking for. Would it be safe to assume months and weeks would be calculated similiarly i.e. weeks= Div ( Elapsed Time ; 11342.8571 ) & " weeks" , Div ( Elapsed Time ; 86400 ) & " days, " & Mod ( Elapsed Time ; 86400 ) / 3600 & " hours" where 11342.8571 represents a week in seconds or might it entail a somewhat different calculation?

Jack

I cannot reproduce your problem. Using =

```Let ( [
Rental_Date_Start = Date ( 1 ; 22 ; 2015 ) ;
Rental_Date_End = Date ( 1 ; 23 ; 2015 ) ;
Rental_Start_Time = Time  ( 7 ; 0 ; 0 ) ;
Rental_End_Time = Time ( 8 ; 0 ; 0 )
] ;
GetAsNumber (
( Rental_Date_End - Rental_Date_Start ) * 86400 + Rental_End_Time - Rental_Start_Time
)
)```

returns 90000 as expected.

Try something like =

`Div ( ElapsedSeconds ; 86400 ) & " days, " & Mod ( ElapsedSeconds ; 86400 ) / 3600 & " hours"`

My previous post referenced the wrong info... it should have indicated as above.

##### Share on other sites
Would it be safe to assume months and weeks would be calculated similiarly i.e. weeks= Div ( Elapsed Time ; 11342.8571 ) & " weeks" , Div ( Elapsed Time ; 86400 ) & " days, " & Mod ( Elapsed Time ; 86400 ) / 3600 & " hours" where 11342.8571 represents a week in seconds or might it entail a somewhat different calculation?

The Div() & Mod() pattern works with any division into larger units. However:

1. I am not sure how you came up with "1342.8571 represents a week in seconds" ; the number of seconds in a week is 7 * 24 * 60 * 60 = 604800;

2. To divide the total seconds into weeks, days and hours, you must extract the days from the remainder after extracting the weeks, e.g. =

```Let ( [
w = Div ( Elapsed Time ; 604800 ) ;
r = Mod ( Elapsed Time ; 604800 ) ;

d = Div ( r ; 86400 ) ;
h = Mod ( r ; 86400 ) /3600
] ;
w & " weeks " & d & " days, " &  h & " hours"
)
```

3. You can't really do the same thing with months, because each month has a different length in seconds.

##### Share on other sites

Comment,

Sorry for the delay in responding- life has a way of getting in the way. Your suggestion works wonderfully ... and no I do not recall

how I ended up with that particular number representing # of secs in a week- either late in the evening or not enough caffeine.

Thanks again for sharing your expertise with the community.

Jack

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...