Capturing Start/End Times and Calculating Duration

[Sue Frost]

I'm trying to put together a record producer that will create timesheet records in a custom application. 

Users will complete a service catalog item indicating how many hours each day that they worked. They need to provide date, start and end times and the lunch duration. These users are not tech savvy, so this structure of data entry was required.

Upon submit, the record producer will create this record:

The first issue we ran into was with Daylight Savings time. We were seeing records created where the employee had worked 8 to 4 but the time captured was 7 to 3.

I've now got the input form using a choice list for the start and end time components. (This makes data entry easier and solves a 'round to the quarter hour' issue as well.) I've added the choice 'values' so that they are recognizable times:

And then my code turns the time into a full formatted time value so that I can calculate the duration on save of the timesheet:

        var start_concat = "1970/01/01" + " " + start_time;
        var gdtS = new GlideTime();
        gdtS.setDisplayValue(start_concat, "dd/MM/yyyy hh:mm:ss");
        var startTimeForField = gdtS.getDisplayValue();

         var end_concat = "1970/01/01" + " " + quit_time;
        //var end_concat = "1970/01/01 00:15:00";  // + " " + quit_time;
        var gdtQ = new GlideTime();
        gdtQ.setDisplayValue(end_concat, "dd/MM/yyyy hh:mm:ss");
        var quitTimeForField = gdtQ.getDisplayValue();

        gs.info("GRTW - startTimeForField = " + startTimeForField);
        gs.info("GRTW - quitTimeForField = " + quitTimeForField);
        gs.info("GRTW - start_date = " + start_date);
        gs.info("GRTW - start_time = " + start_time);
        gs.info("GRTW - quit_time = " + quit_time);
        gs.info("GRTW - lunch_duration = " + lunch_duration);

All that said, the code is working exactly as I'd hoped ... except when I choose a time over the noon hour (12:00 PM / 12:15 PM, etc.) Those times are being read as midnight, not noon.

My log statements show the data being set incorrectly:

Do I need to show these choices in a different format? How do I ensure that 'noon' hours are recognized properly?

Thanks!

[Sue Frost · Accepted solution]

Replying to myself with my eventual solution.

Setting the Value of all the time choices to the format "12:15:00 PM" has worked.

And my code ended up quite simple - with the Value formatted, I didn't have to do anything special in the record producer:

    //* * * Parse the MRVS time sheet  * * *
    //Since Servicenow will produce one record the array processing is set to skip the last array, the first records will be written in the loop  
    var timesheet = producer.timesheet_submission_2;
    var timesheetobj = JSON.parse(timesheet);
    var start_date;
    var start_time;
    var quit_time;
    var lunch_duration;
    var email_desc;
    var emailArray = [];
    var gtimeS = new GlideTime();
    var gtimeQ = new GlideTime();

    if (timesheetobj.length == 0) {
        msg = gs.getMessage('gradual.return.to.work.blank.submission');
        gs.addInfoMessage(msg);
    }

...

   for (var i = 0; i < (timesheetobj.length - 1); i++) {

        calculateFields();

        //Write out the record - net hours is calculated by business rule
        var gr2 = new GlideRecord('x_enig_med_absence_medical_absence_timesheet');
        gr2.initialize();
        gr2.employee_start_time = start_time;
        gr2.employee_end_time = quit_time;
        gr2.date_worked = start_date;
        gr2.grtw_status = 'submitted';
        gr2.lunch_duration = lunch_duration;
        gr2.parent = gr.sys_id;
        gr2.insert();

...

function calculateFields() {

    start_date = timesheetobj[i].sr_grtw_date_worked;
    start_time = timesheetobj[i].sr_grtw_start_time;
    quit_time = timesheetobj[i].sr_grtw_end_time;

    if (timesheetobj[i].sr_grtw_lunch_duration == undefined) {
        lunch_duration = '0';
    } else {
        lunch_duration = timesheetobj[i].sr_grtw_lunch_duration;
    }
    gtimeS.setValue(start_time);
    gtimeQ.setValue(quit_time);
}

I added a business rule to the Timesheet table to calculate the duration. This also allows for manual updates to be calculated properly.

The rule runs when the Start, End times or Lunch Duration changes.

(function executeRule(current, previous /*null when async*/ ) {
    var start_time = current.employee_start_time;
    var quit_time = current.employee_end_time;
// Call Script include  to determine difference between start and quit time
	
    var dtUtil = new global.ServerDateTimeUtils();
    var targetduration = dtUtil.getDateTimeDiff(start_time, quit_time, 'hour');   
    var hour_worked_calc = (targetduration - current.lunch_duration);
    hour_worked_calc = hour_worked_calc.toFixed(2);
   
    if (hour_worked_calc > 5 & current.lunch_duration == 0) {
        gs.addErrorMessage('Lunch must be entered if hours of work exceeds 5 hours');
    } else {
        current.hours_worked = hour_worked_calc;
    }
    
})(current, previous);