Custom Function Calc

[Jose D]

I have a custom function calculating Elapsed Time from when an email comes in to the database. heres the function:

Let (

[

seconds = If( IsEmpty(tsEnd) ; "" ; tsEnd- tsStart) ;

minutes = seconds/60 ;

hours = minutes/60 ;

days = hours/24;

months = days/30;

years = months/12;

displaySeconds = Mod(seconds ; 60);

displayMinutes = Int(Mod(minutes; 60));

displayHours = Int(Mod(hours ; 24));

displayDays = Int(Mod(days; 30));

displayMonths = Int(Mod(months; 12));

displayYears = Int(Div(months; 12))

];

Case(

IsEmpty(tsEnd); "" ;

displayDays & "d " & displayHours & "h " & displayMinutes & "mins"

) // end Case

) // end Let

when an emails age is under an hour, is displays as follows:

"0d 0h 23mins"

I would like it to display as "23mins"

When an emails age is over an hour but under one day it displays:

"0d 4hr 32mins"

i would like it to be displayed as "4hrs 32mins"

The below Case statement controls the output:

Case(

IsEmpty(tsEnd); "" ;

displayDays & "d " & displayHours & "h " & displayMinutes & "mins"

) // end Case

i need to figure out the calculation or statement to enter here so that:

1. IF days = 0 then "(displayHours & "h " & displayMinutes & "mins")"

elapsedtime.zip

[Lee Smith]

I attempted to download your file, and it failed to decompress.

Here is a Custom Function that might help you. Link, if not, look around Brian Dunning's site as there are others.

BTW, you don't reflect that you have the Developer or Advance Edition of FileMaker in your profile, If you are using one of these, you need to update your Profile.

Lee

[Jose D]

SOrry, just updated my info as i see it can be useful. Here is another try at uploading my sample data, i got the custom function from that same website, the problem is the one you sent me does not tell me the number of minutes.

I dont know why my zip files are corrupt, but what below are two files, please download the larger of the two and rename the file from .zip to .fp7

its not compressed, i just changed the extension so that the server at fmforum allows me to upload it without becoming corrupt

elapsedtime.zip

elapsedtime.zip

Edited September 26, 2009 by Guest

[comment]

its not compressed, i just changed the extension

Please don't do that.

As for your calculation, try:

Let ( [

e = tsEnd- tsStart ;

d = Div ( e ; 86400 ) ;

r = Mod ( e ; 86400 )

] ;

Case ( not IsEmpty ( tsEnd ) ; 

Case ( d ; d & "d " ) & Hour ( r ) & "h " & Minute ( r ) & "mins "

)

)

[Lee Smith]

You should remove all of your attachments.

You have to actually Zip the files, or they do NOT come through so that we can open them.

These two files acted the same as your first file. i.e. they do not open. Zipping a file does not corrupt them it.

Lee

Edited September 26, 2009 by Guest

[Jose D]

thanks, that is perfect. i was trying to understand the function but didnt quite get it. i was trying to figure out how i could get rid of the 0h so that anything under an hour only displays the mins. this is not important but would be nice to have

thanks in advanced,

[comment]

Just do the same thing for the hours as for the days - put them inside a Case() statement. But what do you want as the result when hours = 0 but days > 0?

[Jose D]

thanks im going to try what you just mentioned

as far as your follow-up question:

0days = Xhrs Xmins

0days 0hrs = Xmins

[comment]

The question was about 2days 0hrs 25mins. Do you want the hours to show in such case?

[Jose D]

i dont want anything with a zero, so that would be 2days 25mins

[comment]

Well, then:

Let ( [

e = tsEnd- tsStart ;

d = Div ( e ; 86400 ) ;

r = Mod ( e ; 86400 ) ;

h = Hour ( r ) ;

m = Minute ( r )

] ;

Case ( tsEnd ; 

Case ( d ; d & "d " ) & 

Case ( h ; h & "h " ) &

Case ( m ; m & "mins " )

)

)