Brian Dunning's FileMaker Custom Functions

TimeEasy ( mytime ; transtime )

Time auto-formatting with assumed AM & PM during normal working hours. Allows input of abbreviated numbers, i.e. 7 for 7:00 AM and 3 for 3:00 PM. Allows override by use of one letter, for instance 10p for 10:00 PM.

  Average rating: 4.6 (27 votes) Log in to vote

Steve Gerow   Steve Gerow
Abrazos Data Consulting
http://www.abrazosdata.com

Share on Facebook Share on Twitter

  Sample input:
10
1020p
833
  Sample output:
10:00 AM
10:20 PM
8:33 AM

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

4/13 - corrections for 3PM and entry of number 12

11/12 - updated to include legal time entry formats except Military time not allowed.

5/12 - updated for use in Time Field with OnObjectValidation trigger.

Useful for easy time entry in a time tracking system.

 

Comments

Michael Phillips   Michael Phillips, New York, NY
Apr 22, 2009
Great function. Thanks!
 
Søren Dyhr   Søren Dyhr, Copenhagen, Denmark
Mar 19, 2010
While the readbility might be champion, isn't the utilization of calc'functions somewhat meager:

The same behaviour could be written this way, owing tribute to typecasts....

Let(
[
tt =
Int(
Substitute(
Choose( Length( textfield ) - 1; "ç00"; "ç00"; "ç"; "ç" );
"ç";
textfield
)
);
ts = Case( Div( tt - 100; 1200 ); 100 + Mod( tt - 100; 1200 ); tt )
];
Replace( ts; Length( ts ) - 1; 0; ":" ) & " " &
Case(
Position( Lower( textfield ); "p"; 1; 1 );
"P";
Div( tt - 100; 1200 );
"P";
"A"
) & "M"
)

--sd
 
Nina   Nina, New York
Nov 29, 2012
Just an FYI...if you use this and the user enters 3 PM or 3PM it results in 3:03 PM. I changed it to what's shown below and all works well.

Let ( [
raw = GetAsText ( mytime ) ;
rawtime = GetAsNumber ( raw) ;

inter =

Case ( Length ( rawtime ) = 1 ; rawtime & "00" ;
Case ( Length ( rawtime ) = 2 ; rawtime & "00" ;
Case ( Length ( rawtime ) ≥ 3 ; rawtime
))) ;

suffix = // allows override of assumed suffix, 10p becomes 10:00 PM
Case ( PatternCount ( Right ( mytime ; 2 ) ; "a" ) ; " AM" ;
Case ( PatternCount ( Right ( mytime ; 2 ) ; "p" ) ; " PM" ))
];


Case ( inter > 0 and inter < transtime ; Left ( inter ; 1 ) &":"& Right ( inter ; 2 ) & If ( not IsEmpty ( suffix ) ; suffix ; " PM" ) ;
Case ( inter ≥ transtime and inter < 1000 ; Left ( inter ; 1 ) &":"& Right ( inter ; 2 ) & If ( not IsEmpty ( suffix ) ; suffix ; " AM" ) ;
Case ( inter ≥ 1000 and inter < 1200 ; Left ( inter ; 2 ) &":"& Right ( inter ; 2 ) & If ( not IsEmpty ( suffix ) ; suffix ; " AM" ) ;
Case ( inter ≥ 1200 and inter < 1260 ; Left ( inter ; 2 ) &":"& Right ( inter ; 2 ) & If ( not IsEmpty ( suffix ) ; suffix ; " PM"
)))))
)
 
david   david, CA
Apr 22, 2013
Søren Dyhr - solution is clean and simple, but with one bug. When entering '00' or '12' the AM and PM are reversed.
 
david   david, CA
Apr 22, 2013
Søren Dyhr - 1145p does not work either
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.