Jump to content

TimeStamp Calculation


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

Recommended Posts

Hello FL gurus,

I am trying to create a calculation using the "Timestamp" function but can't get the result I need. I am hopping someone can guide me.

I have three two fields and two buttons

Start Time (button once press it enters the time in the field using "Set Field" and "Get (Get ( CurrentTimeStamp )"

End Time  (button once press it enters the time in the field using "Set Field" and "Get (Get ( CurrentTimeStamp )"

I also have a calculation field (Start - End) with a result of "timestamp"

is if possible to ger a result to show elapsed days and time

e.g start 1/1/2016 1:00PM - end 1/1/2016 2:00PM I would like the result to be 0 day and 1 hour

e.g start 1/1/2016 1:00PM - end 1/2/2016 2:00PM I would like the result to be 1 day and 1 hour

or I am better using two fields one for time and one for date

Thanks

RudyM

 

Edited by rudym88
Link to comment
Share on other sites

Hi Rudy, 

 

Why do you use "Get" twice? 

Your result cannot be a timestamp. It can be minutes or it can be converted to hours and days but a timestamp does not equal to elapsed time. The time end - time start should result in seconds if I remember correctly. So you'll just need to calculate it into the days and hours. 

 

So you'll need to do a little math to get that. I'm sure there are custom functions out there for it on Brian dunning.com.

Edited by Agnes Riley
Seconds
Link to comment
Share on other sites

GetAsTimestamp ( "1/2/2016 3:00 PM" ) - GetAsTimestamp ( "1/1/2016 2:00 PM " )
gives a result of 25:00:00 which is actually 90,000 seconds.

It's actually easier to do elapsed time using timestamps rather than separate time and date fields, because FileMaker will do the math for you when spanning multiple days, months or years. Then as Agnes says you need to build your own calculation to construct the text output you want. I took a quick stab at it:

Let([
  hrs = endTime - startTime ;  // your timestamp fields
  hr = Hour(hrs) ;
  d = Int( hr/24 ) ; 
  secs = GetAsTime( GetAsNumber ( hrs ) - ( d * 86400 ) ); 
  h = Hour( secs ) ;
  pluralD = Case( d <> 1 ; "s" ) ; 
  pluralH = Case( h <> 1 ; "s" )
 ];

   d & " day" & pluralD & " and " &
   h & " hour" & pluralH 
)

 

For simplicity's sake I didn't bother with minutes or seconds, but those would be easy enough to add.

Link to comment
Share on other sites

5 hours ago, rudym88 said:

e.g start 1/1/2016 1:00PM - end 1/2/2016 2:00PM I would like the result to be 1 day and 1 hour

The question is not defined well enough, because we don't know how to handle partial hours. Assuming you only want to show the nearest hour (i.e. round the elapsed time to the nearest hour), you could do simply:

Let ( [
dur = End - Start ;
days = Div ( dur ; 86400 ) ;
time = Mod ( dur ; 86400 ) ;
hours = Round ( time / 3600 ; 0 )
] ;
days & " days " & hours & " hours"
)

This assumes Start and End are Timestamp fields.

 

5 hours ago, rudym88 said:

or I am better using two fields one for time and one for date

No, that would only make it more complicated.

 

Edited by comment
Link to comment
Share on other sites

32 minutes ago, rudym88 said:

how about if I want the minutes and seconds.

That too is rather simple:

Let ( [
dur = End - Start ;
d = Div ( dur ; 86400 ) ;
time = Mod ( dur ; 86400 ) ;
h = Hour ( time ) ;
m = Minute ( time ) ;
s = Seconds ( time )
] ;
d & " days, " & h & " hours, " & m & " minutes and " & s & " seconds"
)

Or, if you want to get fancy:

Let ( [
dur = End - Start ;
d = Div ( dur ; 86400 ) ;
time = Mod ( dur ; 86400 ) ;
h = Hour (time) ;
m = Minute ( time ) ;
s = Seconds ( time ) ;
result = List (
If ( d ; d & " day" ) & If ( d > 1 ; "s" ) ;
If ( h ; h & " hour" ) & If ( h > 1 ; "s" ) ;
If ( m ; m & " minute" ) & If ( m > 1 ; "s" ) ;
If ( s ; s & " second" ) & If ( s > 1 ; "s" ) 
) ;
lastPos = Position ( result ; ¶ ; Length ( result ) ; -1 )
] ;
Substitute ( Replace (result ; lastPos ; 1 ; " and " ) ; ¶ ; ", " ) 
)

 

 

Edited by comment
Link to comment
Share on other sites

  • 2 months later...

great formula, I am new to Filmmaker and was tasked to do a log for hours worked and elapse time between entry. can someone help me.

I am doing something similar except I have four fields;

Start_Date

End_Date

Start_Time

End_Time

 

I need to calculate the elapse time, I would like the output to say 1 day\days 1 hour\hours 1 minute\minutes

Also would like the output in the same field

thank you for any help.

Jose

 

 

Edited by JMart
Link to comment
Share on other sites

15 minutes ago, JMart said:

I am doing something similar except I have four fields;

Start_Date

End_Date

Start_Time

End_Time

Define:

dur = Timestamp ( End_Date ; End_Time ) - Timestamp ( Start_Date ; Start_Time ) ;

and continue as before.

Link to comment
Share on other sites

This topic is 2797 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.