An interesting subject came up in another post about this.
I have a list of songs in a table. One of the fields is duration, which is entered-not calculated.
The field is set up as a Time field.
During data entry, the user must enter 00:04:30 (above), and on the layout the field is formatted as shown in the left grab. If I leave the field and go back to it, I see the dataformat on the right. .
Ultimately, I'm looking for an easier way to enter the duration as xx:xx:xx is fairly cumbersome.
Any ideas?
Call the CF in the auto enter calculation of the field.
[Edit:] Make sure to uncheck "Do not replace existing field value"
[Edit:] Now the CF is on Brian Dunnings website: https://www.briandunning.com/cf/2460
[Edit:] I have added the substitution of colons at the beginning of the cf. Now you can modify the time in the field in the case you have mistyped.
[Edit:] Call me the "500TimesOneMoreThing-Guy"! Every few minutes there’s something I have to add or correct. This time I have corrected a mistake in the cf.
[Edit – once more:] Added a check if the string is longer than 6 digits and handles that. Before the string simply was cutted. Now it returns a "?" if the string is too long.
To call the custom function in the auto enter calculation of your field "duration" you write "FormatAsTime( Self )". So the value you enter into the field is given to the custom function that returns the formatted value.
Every time you enter a too long string or a minute value with more than 59 minutes or seconds with more than 59 seconds the cf returns a "?" to show there’s an error.
[Edit:] But first you have to create the custom function. Did you do that?
To create the custom function go to "File > Manage > Custom Functions…"
Click "New" – enter "FormatAsTime" into the field "Function Name". In the field below you type "~time". Then click on the + on the right side of the field. Into the big field you copy the code of the new function:
/*
Typing times into time fields is cumbersome: ##:##:##
With this custom function, you only need to enter the numbers. Without colons and leading zeros before the first number.
For entering 4 minutes and 25 seconds just type 425. The CF gives back 00:04:25.
Call this CF in the auto enter calculation of a time field and make sure to uncheck "Do not replace existing field value".
*/
Let ( [
~time = Substitute( ~time ; ":" ; "" );
~lengthOfTimeString = Length( ~time );
~timeWithLeadingZeros = Right ( "0000000000" & ~time ; ~lengthOfTimeString );
~hours = Left ( ~timeWithLeadingZeros ; ~lengthOfTimeString - 4 );
~minutes = Middle ( ~timeWithLeadingZeros ; 3 ; 2 );
~seconds = Right ( ~timeWithLeadingZeros ; 2 );
~timeFormatted = ~hours & ":" & ~minutes & ":" & ~seconds;
~validateInput = If ( GetAsNumber ( ~hours ) < 24 AND GetAsNumber ( ~minutes ) < 60 AND GetAsNumber ( ~seconds ) < 60 ; true ; false );
~end = ""];
// Calculation
If ( ~validateInput ; ~timeFormatted ; "?")
)
Click the OK button and then the OK button in the Manage Custom Functions window and you’re done! Now you can use the custom function as described in my previous answer.
Sorry I'm such a noobie - but I'm learning....
I added the CF and entered the FormatAsTime(Self) statement in the calculation. I duplicated the duration field for testing (duration copy) until I get it worked out.
The first left field is my original; the right is the duration copy.
I've changed the field type from time to text to number all with the same results (I entered 401 in the second field).
.
Call me the "500TimesOneMoreThing-Guy"! Every few minutes there’s something I have to add or correct. This time I have corrected a mistake in the cf.