FormatTime_NoSeconds ( a.time )

Formats a time to a consistent AM/PM without any seconds.

Average rating: 4.5 (23 votes) Log in to vote

David Jondreau - Show more from this author
Wing Forward Solutions, LLC
http://www.wingforward.net

Sample input:

FormatTime_NoSeconds ( "2:03:12 pm" )

Sample output:

2:03 PM

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

Let([

_time = a.time ;
null = "" ;

t = GetAsTime ( _time ) ;
isT = ( t = _time ) ;

digitsCol = "0123456789:" ;

emp = IsEmpty ( t ) ;
filtT = Filter ( _time ; digitsCol ) ;

col = PatternCount ( filtT ; ":" ) ;
hms = If ( col > 1 ; LeftWords ( t ; 2 ) ) ;

digits = Substitute ( filtT ; ":" ; Null ) ;
lenDig = Length ( digits ) ;


h1 = Case ( lenDig  ≤  2 ; digits ; lenDig = 3 ; Left ( digits ; 1 ) ; Left ( digits ; 2 ) ) ;
m1 = If ( lenDig  ≤  2 ; "00" ; Right ( digits ; 2 ) ) ;

h = If ( col = 0 ;  h1 ; Hour ( filtT )  ) ;
m = If ( col = 0 ; m1 ; Right ( "00" & Minute ( filtT ) ; 2 ) ) ;
h = Mod ( h ; 12 ) ;
h = If ( h = 0 ; 12 ; h ) ;

post = "ampmnoonmidnight" ;
post = post & Upper ( post ) ;
ampm1 = Filter ( _time ; post) ;
ampm2 = If ( ( h < 8 ) or ( h = 12) ; "PM" ;  "AM" ) ;
ampm3 = If ( t < GetAsTime ( "12:00:00" ) ; "AM" ; "PM" ) ;
need_ampm = not ( PatternCount ( ampm1 ; "am" ) or PatternCount ( ampm1 ; "pm" ) or  PatternCount ( ampm1 ; "noon" ) or PatternCount ( ampm1 ; "midnight" )  ) ; 
ampm =  Case ( need_ampm and isT ; ampm3 ; need_ampm ; ampm2 ; ampm1 ) ;

final = h & ":" & m & " " & ampm ;
final = Upper ( final ) ;
result = If ( emp ; Null ; final )
];
result)

Time fields are a little funny. They store what is entered. So you can put in 1 PM or 2:31:03 am and that's what it will store. You can change what a field displays as using the formatting options, but when you click into a field it shows what is being stored. And you'll need to remember to format any output if you want a consistent look.

Often, the seconds column are unnecessary.

This function takes a time field (entered in US format, not military time), removes the seconds, and formats it so the same style of time will be stored.

Comments

		comment, VR Apr 8, 2015
1. Your formula breaks on an undefined function(?) Null. 2. Couldn't this be simpler? For example: Mod ( Hour ( time ) - 1 ; 12 ) + 1 & SerialIncrement ( ":00" ; Minute ( time ) ) & Case ( time < 43200 ; " AM" ; " PM" ) seems to return the same results as yours would. Note that both fail with times greater than 24 hours (25:00:00 is 1:00 AM, not PM), but that's easy (at least in my version) to fix.

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

FormatTime_NoSeconds ( a.time )

Comments

Support this website.

Support this website.