Jump to content

Calculate duration between 2 date/time events - weird result


This topic is 2007 days old. Please don't post here. Open a new topic instead.

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

 

post-88128-0-37759700-1421874723_thumb.p

post-88128-0-10842300-1421874912_thumb.j

Link to post
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"
Link to post
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

Link to post
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.

Link to post
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

Link to post
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.

Link to post
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.

  • Like 1
Link to post
Share on other sites
  • 2 weeks later...

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

Link to post
Share on other sites

This topic is 2007 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.