In order to speed up data entry I am trying to create calculation that formats any four valid numbers, (with or without a colon), to the format required by FileMaker for a time field i.e. hh:mm

In other words-

1. if the person enters the time correctly as hh:mm the calculation does nothing.

2. if the person enters hhmm the colon is inserted

3. if an incorrect 24hour time is entered eg 26:35 or 2a:25 the calculation returns for example a question mark.

At present I have three fields that I use related to the duration of a procedure:

StartTime, FinishTime and Duraton.

For all three the type of field= “time”

StartTime and FinishTime are entered manually, are set to “require strict data type= time of day”

Duration is an “auto enter calculation” =

If (Finish time="";0;

If ( Finish time ≥ Start time ; Finish time - Start time ; Finish time - Start time + 86400 ))

This allows for the event that a procedure might start before and end after midnight.

For my “Duration” auto enter calculation to work the StartTime and FinishTime fields are, as I mentioned, “time” fields as opposed to “number” type fields.

To get back to my original question of trying to insert a colon into the middle of four numbers one of the problems I have is that if I change the field type for StartTime and FinishTime from “time” to “calculation” then the “Duration” calculation fails.

For the first line of my calculation to remove invalid numbers or if letters are entered in error guess I might need something like:

Filter (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)

Then I get a bit stuck….

I have tried, unsuccessfully, using some ideas from this post:

http://fmforums.com/forum/showtopic.php?tid/195907/post/293736/hl/time+format/fromsearch/1/#293736

Which leads me to another question, in Cardiofuse’s calculation he uses for instance:

Reformat=(PatternCount(Th eText;":")=0);

and

RawNumber=GetAsNumber(The Text) ];

Are “Reformat” and “RawNumber” calculation functions like “If” or “Case” because I cannot seem to find them in the list of “all functions by name”?

Sorry so long winded but any help would be greatly appreciated

Alan

Why enter the ":" at all.

If you split the data entry to the "hh" and "mm" into 2 fields then you can have a (hidden) calculation field the you can format as you want.

You can also validate the input to exist only between the ranges:

1-23 for hours

0-59 for minutes

...trying to insert a colon into the middle of four numbers

Validation:

yourTimeField < 86400

AutoEnter a calculated value:

Case(

Length ( yourTimeField ) = 3 ; Replace ( yourTimeField ; 2 ; 0 ; ":" ) ;

Length ( yourTimeField ) = 4 ; Replace ( yourTimeField ; 3 ; 0 ; ":" ) ;

time

)

Thank you both.

Both ideas are great!