Transform map import to time field

[samadam]

I am trying to import data into table using transform map from excel. I have time fields that I want to import into. Data is in the format 1:00:00 pm. I set the transform map to hh:mm:ss as i dont see option to include am/pm. It is just doing 12:00 for all the records. Is there a way to fix this?

[Robert H]

Hello @samadam ,

Please remove the Field Map for the time field and instead add the following Transform Script (type: on Before).

Replace my source and target field names with yours.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var input = source.u_my_time;

    var regex = /^(\d?\d):(\d{2}):(\d{2})(?:\s?([ap]m))?$/i;
    var match = input.match(regex);
    if (!match || match.length < 4) return;

    var hours = match[1];
    var minutes = match[2];
    var seconds = match[3];

    if (match.length === 5 && match[4].toUpperCase() === 'PM' && hours <= 12) {
        hours = parseInt(hours) + 12;
    }
    hours = ('00' + (hours % 24)).slice(-2);

    target.u_my_time = gs.getMessage('1970-01-01 {0}:{1}:{2}', [hours, minutes, seconds]);

})(source, map, log, target);

Regards,

Robert