FormatTimestamp ( theTimestamp ; format )

Formats a timestamp using the excel format method

Be the first to rate this function Log in to vote

Paul - Show more from this author
Peak 14 LLC
http://www.peak14.solutions

Sample input:

FormatTimestamp ( GetAsTimestamp ( "1/1/2000 1:00pm" ); "yyyy-mm-ddTHH:ii" )

Sample output:

2000-01-01T13:00

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

/*
 *  CUSTOM FUNCTION
 *    FormatTimestamp ( theTimestamp; format )
 *
 *  DESCRIPTION
 *    Formats a timestamp, date or time using the standard excel/google sheet function formatting
 *
 *  PARAMS
 *    timestamp: The timestamp, date or time to format
 *    format: The format as a text string. Call function with format as "?" for more details
 *
 *  EXAMPLES
 *    FormatTimestamp ( GetAsTimestamp ( "12/01/2000 1:00PM" ); "ddd, dd mm yyyy - HH:ii")
 *      "sat, 01 12 2000 - 13:00"
 *    FormatTimestamp ( GetAsTimestamp ( "01/01/2011 4:05PM" ); "d/m/yy hh:ii ap")
 *      "1/1/11 4:05 pm"
 *
 *  DEPENDENCIES
 *    --None--
 *
 *  CHANGELOG
 *    2021-09-17: Paul McCudden: Created - Time section incomplete
 *    2022-05-26: Paul McCudden: Mostly finished time section - Millisecond section incomplete
*/

While (
    [
        ~theTimestamp = If ( IsEmpty ( theTimestamp ); Get ( CurrentTimestamp ); theTimestamp ); 
        ~date = GetAsDate ( ~theTimestamp );
        ~time = GetAsTime ( ~theTimestamp );
        ~format = If ( IsEmpty ( format ); "?"; format);
        ~escapeCharater = "\\";

        // Avoid loop if they want help text
        ~toProcess = If ( ~format <> "?"; ~format );
        ~formatted = If ( ~format = "?";
            "FormatTimestamp ( theTimestamp; format ):¶"&
            "Formats theTimestamp using the excel/google sheet conventions¶¶"&
            "Year Codes:¶"&
            "    yy: The year as 2 digits¶"&
            "    yyyy: The year as 4 digits¶"&
            "Month Codes:¶"&
            "    m: The month as a 1 or 2 digits¶"&
            "    mm: The month as 2 digits¶"&
            "    mmm: The short name of the month¶"&
            "    mmmm: The full name of the month¶"&
            "    mmmmm: The first letter of the month name¶"&
            "Day Codes:¶"&
            "    d: The day as 1 or 2 digits¶"&
            "    dd: The day as 2 digits¶"&
            "    ddd: The short name of the day of the week¶"&
            "    dddd: The full name of the day of the week¶"&
            "    ddddd: The first letter of the day of the week¶"&
            "Hour Codes:¶"&
            "    HH: The hours on a 24-hour clock¶"&
            "    hh: The hours on a 12-hour clock¶"&
            "Other Time Codes:¶"&
            "    ii: The minutes¶"&
            "    ss: The seconds¶"&
            "    .000: The milliseconds as a fraction (to the same number of digits) - incomplete¶"&
            "    ap: For displaying hours based on a 12-hour clock and showing am or pm¶"&
            "    AP: For displaying hours based on a 12-hour clock and showing AM or PM¶"&
            "    Ap: For displaying hours based on a 12-hour clock and showing Am or Pm¶"&
            "Escaped Values:¶"&
            "    Any value can be enclosed in \ to be escaped¶"&
            "    Note that two \ will need to be included in text strings¶"&
            "Help:¶"&
            "    If format is ?, this function will output help text"
        )
    ];

    not IsEmpty ( ~toProcess );
    
    [
        ~processing = Left ( ~toProcess; 1 );

        // Grab full escaped value if we're processing the start of an escape
        ~processing = If ( ~processing = ~escapeCharater;
        
            Case ( 
                
                // If there's no ending escape character, just return the escape character
                Position ( ~toProcess; ~escapeCharater; 2; 1 ) = 0;
                ~processing;

                // Grab all the characters until the next escape character
                Left ( ~toProcess; Position ( ~toProcess ; ~escapeCharater ; 2 ; 1 ) )
            );
            ~processing
        );

        // If they are doing ap, grab the "p"
        ~processing = If (
            ~processing = "a" and Right ( Left ( ~toProcess; 2 ); 1 ) = "p";
            Left ( ~toProcess; 2 );
            ~processing
        );

        // If there are multiple of the same character in a row, grab all of that character - only do this section if processing is a single character still
        ~processing = If ( Length ( ~processing ) = 1;
            While (
                [
                    ~processCheck = ~processing;
                    ~processing2 = ~processing;
                    ~next = Right ( Left ( ~toProcess; 2 ); 1 )
                ];

                ~processCheck = ~next and Length ( ~processing2 ) < Length ( ~toProcess );

                [
                    ~processing2 = ~processing2 & ~next;
                    ~next = Right ( Left ( ~toProcess; Length ( ~processing2 ) + 1 ); 1 )
                ];

                ~processing2
            
            );
            ~processing
        );
        
        // We have the full processing section. Cut it out of what's left to process
        ~toProcess = Right ( ~toProcess; Length ( ~toProcess) - Length ( ~processing ) );

        // Format the processing section we have
        ~formatted = ~formatted & Case (

            // Escape Character
            Left ( ~processing; 1 ) = ~escapeCharater; Substitute ( ~processing; ~escapeCharater; "" );

            // Year
            ~processing = "yy"; Right ( GetAsText ( Year ( ~date ) ); 2 );
            ~processing = "yyyy"; GetAsText ( Year ( ~date ) );

            // Month
            ~processing = "m"; GetAsText ( Month ( ~date ) );
            ~processing = "mm"; Right ( "0" & Month ( ~date ); 2 );
            Exact ( ~processing; "MMM" ); Upper ( Left ( MonthName ( ~date ); 3 ) );
            Exact ( ~processing; "Mmm" ); Proper ( Left ( MonthName ( ~date ); 3 ) );
            ~processing = "mmm"; Lower ( Left ( MonthName ( ~date ); 3 ) );
            Exact ( ~processing; "MMMM" ); Upper ( MonthName ( ~date ) );
            Exact ( ~processing; "Mmmm" ); Proper ( MonthName ( ~date ) );
            ~processing = "mmmm"; Lower ( MonthName ( ~date ) );
            Exact ( ~processing; "MMMMM" ); Upper ( Left ( MonthName ( ~date ); 1 ) );
            ~processing = "mmmmm"; Lower ( Left ( MonthName ( ~date ); 1 ) );

            // Day
            ~processing = "d"; GetAsNumber ( Day ( ~date ) );
            ~processing = "dd"; GetAsText ( Right ( "0" & Day ( ~date ); 2 ) );
            Exact ( ~processing; "DDD"); Upper ( Left ( DayName ( ~date ); 3 ) );
            Exact ( ~processing; "Ddd"); Proper ( Left ( DayName ( ~date ); 3 ) );
            ~processing = "ddd"; Lower ( Left ( DayName ( ~date ); 3 ) );
            Exact ( ~processing; "DDDD" ); Upper ( DayName ( ~date ) );
            Exact ( ~processing; "Dddd" ); Proper ( DayName ( ~date ) );
            ~processing = "dddd"; Lower ( DayName ( ~date ) );
            Exact ( ~processing; "DDDDD" ); Upper ( Left ( DayName ( ~date ); 1 ) );
            ~processing = "ddddd"; Lower ( Left ( DayName ( ~date ); 1 ) );

            // Hours
            Exact ( ~processing; "HH"  ); Hour ( ~time );
            Exact ( ~processing; "hh" ); Hour ( ~time ) - If ( Hour ( ~time ) > 12; 12 );

            // Minuets
            ~processing = "ii"; Right ( "0" & Minute ( ~time ); 2 );

            // Seconds
            ~processing = "ss"; Right ( "0" & Seconds ( ~time ); 2 );

            // Milliseconds - Need to complete
            ~processing = ".000"; "?";

            // Meridiem Indicator (AM/PM)
            Exact ( ~processing; "AP" ); If ( Hour ( ~time ) > 12; "PM"; "AM" );
            Exact ( ~processing; "Ap" ); If ( Hour ( ~time ) > 12; "Pm"; "Am" );
            Exact ( ~processing; "ap" ); If ( Hour ( ~time ) > 12; "pm"; "am" );
            
            // If there is no match, output what they entered
            ~processing
        )

    ];
    
    ~formatted
)

Formats a timestamp, date or time using the standard excel/google sheet function formatting.

Can find details about the formatting in the comments of the custom function or by using "" or "?" as the format parameter

Comments

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

FormatTimestamp ( theTimestamp ; format )

Comments

Support this website.

Support this website.