Calculate duration between 2 date/time events - weird result

[skingjack2]

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

 

[comment]

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"

[skingjack2]

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

[comment]

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.

[skingjack2]

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

[skingjack2]

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.

[comment]

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.

[skingjack2]

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