TimeStamp Calculation

[rudym88]

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 May 3, 2016 by rudym88

[Agnes Riley]

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 May 3, 2016 by Agnes Riley
Seconds

[Fitch]

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.

[Agnes Riley]

Now that I'm home, I could do a little search. Here's a custom function that will do this for you: http://www.briandunning.com/cf/59 If you have Advanced you can just paste the CF in your solution.

 

Hope this helps,

Agi

[comment]

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 May 3, 2016 by comment

[rudym88]

Thanks for the reply, how about if I want the minutes and seconds.

 

Thanks

Rudy

[comment]

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 May 3, 2016 by comment

[JMart]

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 July 28, 2016 by JMart

[comment]

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.

[JMart]

Sorry, when I modify the "dur" the result is a question mark "?"

Thanks

Edited August 1, 2016 by JMart

[comment]

Does this work for you?

 

Duration.fp7