12 Replies Latest reply on Jun 2, 2014 12:29 PM by erolst

Mimic excel time on a calculated value

I have a calculation that results in a number, and I'm trying to duplicate excel's handling of the same formula.

Sample of the formula:

10.80 / 24 / 60 = 0.0075

In excel, the cells are formatted to display hh:mm:ss, with the resulting time displayed from the above calculation as 0:10:48.

In Filemaker I can return the same 0.0075, but am having trouble converting that into the time as noted above.

I know I'm probably missing an obvious multiplier or formula here, what is it?

• 1. Re: Mimic excel time on a calculated value

0:10:48 mins * 1 / 0.0075 = 86,400, i.e. the number of seconds in a day; 24 * 60 = number of minutes in a day.

Mike Beargie wrote:

Sample of the formula:

10.80 / 24 / 60 = 0.0075

Here's a theory that jives with the observable data …

Let ( [

input = 10.8 ;

intPart = Int ( input ) ;

fracPart = input - intPart

] ;

Time ( 0 ; intPart ; fracPart * 60 )

)

= 0:10:48 when input = 10.8.

• 2. Re: Mimic excel time on a calculated value

I failed to mention the  / 60 portion of the calculation is not a divisor for minutes in an hour. it's a user selected factor.

The entire calculation is:

( % of project * working cost ) / 24 / factor

Here are some more examples with a different divisor:

10.8 / 24 / 70 = 0:09:15

54 / 24 / 90 = 0:36:00

21.6 / 24 / 90 = 0:14:24

340.2 / 24 / 60 = 5:40:12

Sorry, using the factor of 60 was a bad example.

• 3. Re: Mimic excel time on a calculated value

I guess I don't really understand the problem … doesn't

GetAsTime ( ( % of project * working cost ) / 24 / factor  * 86400 )

yield the sample results from the sample inputs?

• 4. Re: Mimic excel time on a calculated value

Here's a simple one once you arrive at the number of minutes.

Al Quimby

1 of 1 people found this helpful
• 5. Re: Mimic excel time on a calculated value

86400 was the answer I was looking for. Thanks, I knew it was a simple multiplier I was missing.

I stared at the excel calculation too long (which does not have a multiplier)

So apparently

10.8 / 24 / 60 in excel equals ( 10.8 / 24 / 60 ) * 86400 in filemaker.

• 6. Re: Mimic excel time on a calculated value

Thanks Allen, erolst solved it above by reminding me that there are 86400 seconds in a day.

• 7. Re: Mimic excel time on a calculated value

Mike Beargie wrote:

So apparently

10.8 / 24 / 60 in excel equals ( 10.8 / 24 / 60 ) * 86400 in filemaker.

I guess that depends on what the definition of “Time” is in Excel – or maybe just on what formatting has been applied to that result cell … I admit bloody ignorance when it comes to Excel – whatever I know comes from writing AppleScripts to get data out of there, and not spend more time than necessary.

Anyway, good to see you got it working.

• 8. Re: Mimic excel time on a calculated value

GetAsTime ( ( 10.8 / 24 / 70 ) * 86400 )

• 9. Re: Mimic excel time on a calculated value

Thanks for your assistance. The week isn’t complete without my Monday afternoon facepalm…

• 10. Re: Mimic excel time on a calculated value

You don’t need to use GetAsTime() if the result of your calculation is time.

• 11. Re: Mimic excel time on a calculated value

Yes, but I never know where these things are getting inserted.  I run into a lot problems when ExecuteSQL returns what looks like numbers, but really are text and having to remember to GetAsNumber those results.  Always frustrating that FileMaker can't just read my mind!  <grin>

• 12. Re: Mimic excel time on a calculated value

Mike Beargie wrote:

You don’t need to use GetAsTime() if the result of your calculation is time.

But in the Data Viewer, in case you prefer a human-readable time result to a largish number  …