ExcelNumberToTime ( ExcelNumber )

Returns the time in correct format for FM

Average rating: 4.1 (31 votes) Log in to vote

Daniele Raybaudi - Show more from this author
ACI
http://www.aci.it

Sample input:

ExcelNumberToTime ( "0,0182549768518519")

ExcelNumberToTime ( "0,5")

Sample output:

0:26:17,23

12:00:00

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*

ExcelNumberToTime custom function

Author:
Daniele Raybaudi

Format: 
ExcelNumberToTime ( ExcelNumber)

Parameter:
ExcelNumber - number - the number representation of time in Excel from 0 to 0,99999999
aka from 00:00:00,000 to 23:59:59,999

Returns the time in correct format for FM

*/

Let([
par = ,0000000115740740922925242163;
hours = Int ( excelNumber / (3600000 * par) );
minutes = Int ( excelNumber / (60000 * par) ) - hours * 60;
seconds = Round ( excelNumber / (1000 * par) ; 3 ) - minutes * 60 - hours *3600
];
Time ( hours ; minutes ; seconds )
)

ExcelNumberToTime custom function

Author:
Daniele Raybaudi

Format:
ExcelNumberToTime ( ExcelNumber)

Parameter:
ExcelNumber - number - the number representation of time in Excel from 0 to 0,99999999
aka from 00:00:00,000 to 23:59:59,999

Returns the time in correct format for FM

Comments

		Desmond, Munich Oct 7, 2013
I have a list of times from my logbook to add. e.g. 0:25 1:34 3:26 4:10 and so on. I have tried using the excel format but this doesn't appear to work. Can anyone help?

		Daniele Raybaudi, ACI Oct 7, 2013
I do ot think that this custom function can solve your problem. Also this isn't a Forum,where you can ask for help. Post the same question to a Forum, like FMForums.com.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

ExcelNumberToTime ( ExcelNumber )

Comments

Support this website.

Support this website.