Time Clock for Employees to show total time worked, subtract lunches, and validate time input

[lso]

Hello,

I'm creating a time clock form for employees and need to calculate the total time they input after they choose a time into work, time for lunch breaks, and time out of work. I'll need to get the total hours worked in a day by calculating the time they clock in and clock out and then subtracting the lunch time used. I will also need to subtract a second lunch if they use another one. I need to validate the times for the fields below to make sure they are input correctly as well as shown below.

1. Time In for Workday (validate less than all other times, default 8:30am)
2. Time Out for Workday (validate greater than all other times, default 5pm)
3. Time Out for Lunch 1 (validate greater than Time In For Workday and less than Time In from Lunch 1, default 12pm)
4. Time In from Lunch 1 (validate greater than Time Out for Lunch 1, default 12:30pm)
5. Time Out for Lunch 2 (validate null or less than Time in from Lunch 2)
6. Time In from Lunch 2 (validate greater than Time Out for Lunch 2)
7. Number of Breaks (default 2)

I've coded the default times for the fields needed as I couldn't do this directly in the SharePoint list as I set the date and time to default for the date the form is started for the fields:

fd.spRendered(function() {
var d = new Date();
d.setHours(8, 30, 0);
fd.field('TimeInWorkday').value = d;
});

fd.spRendered(function() {
var d = new Date();
d.setHours(17, 0, 0);
fd.field('TimeOutWorkday').value = d;
});

fd.spRendered(function() {
var d = new Date();
d.setHours(12, 0, 0);
fd.field('TimeOutLunch1').value = d;
});

fd.spRendered(function() {
var d = new Date();
d.setHours(12, 30, 0);
fd.field('TimeInLunch1').value = d;
});

I was hoping there was a way to do these validations and calculations directly in Plumsail. Any help would be greatly appreciated, thank you.

[mnikitina]

Hello @lso,

You can use the Moment.js library and its plugins to calculate the time between two dates and to substruct time. Please find the code examples in our documentation here.

[lso]

Hello @mnikitina ,

I'm not familiar with JS library and plugin features but I will look into this. Thank you.

[lso]

Hi @mnikitina ,

I got everything working fine until I tried to subtract the Lunch 2 time from the total hours worked. I believe because TimeOutLunch2 and TimeInLunch2 can be set to a null value. These are also values that don't default to a set time in the beginning of a new form. The form freezes when I try to do almost anything after starting a new form when I added this code. The total work time minus the first lunch works good to get the total but when I added the second lunch for testing it went weird. It may be messing up with the moment coding. Here's my code below. Any assistance would be greatly appreciated, thank you.

// Calculate total time worked in a day
requirejs.config({
    paths: {
        moment: "https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment-with-locales.min",
        'moment-business-time': "https://forms.plumsail.com/libs/moment-business-time"
    }
});

fd.spRendered(function() {

    require(['moment'], function(moment) {
        require(['moment-business-time'], function() {

            function calcDiff() {
                var startTime = moment(fd.field('TimeInWorkday').value);
                var endTime = moment(fd.field('TimeOutWorkday').value);
                var startLunch1 = moment(fd.field('TimeOutLunch1').value);
                var endLunch1 = moment(fd.field('TimeInLunch1').value);
                var startLunch2 = moment(fd.field('TimeOutLunch2').value);
                var endLunch2 = moment(fd.field('TimeInLunch2').value);
                
                diff = endTime.workingDiff(startTime, 'hours', 'minutes');
                diffLunch1 = endLunch1.workingDiff(startLunch1, 'hours', 'minutes');
                diffLunch2 = endLunch2.workingDiff(startLunch2, 'hours', 'minutes');
                
                    if (fd.field('TimeOutLunch2').value == null || fd.field('TimeInLunch2').value == null) 
                    {
                        fd.field('TotalTime').value = diff - diffLunch1;
                    } 
                    else 
                    {
                        fd.field('TotalTime').value = diff - (diffLunch1 + diffLunch2);
                    }
            
                console.log(diff);
                console.log(diffLunch1);
                console.log(diffLunch2);
            }

            //Function that defines working hours
            function defineWorkHours () {

                moment.locale('en', {
                    workinghours: {

                        0: ['00:00:00', '23:59:59'],
                        1: ['00:00:00', '23:59:59'],
                        2: ['00:00:00', '23:59:59'],
                        3: ['00:00:00', '23:59:59'],
                        4: ['00:00:00', '23:59:59'],
                        5: ['00:00:00', '23:59:59'],
                        6: ['00:00:00', '23:59:59'],
                    }
                });
            }

            defineWorkHours ();

            // Calling function when the user changes times
            fd.field('TimeInWorkday').$on('change', calcDiff);
            fd.field('TimeOutWorkday').$on('change', calcDiff);
            fd.field('TimeOutLunch1').$on('change', calcDiff);
            fd.field('TimeInLunch1').$on('change', calcDiff);
            fd.field('TimeOutLunch2').$on('change', calcDiff);
            fd.field('TimeInLunch2').$on('change', calcDiff);

            // Calling function on form loading
            calcDiff(value, 'TotalTime');
        });
    });
});

[mnikitina]

Hello @lso,

The code looks fine.

When does the form freezes? Are you getting any errors in the browser console(F12)?

Please also try to debug the code, you can find the instructions in this post:

Debug Forms Designer JS Code

Dear @himansh, The easiest option to debug code is to run it directly from browser's console. First, you need to add variables that you want to work with to the global window, with the following lines in JS editor: window.fd = fd; window.$ = $; window.pnp = pnp; Then, run any code pieces in browser's console without any problems. The only issue is that you won't be able to run them inside the events, the form will already be loaded. Most of the time, you can just wrap the code after testing …

[lso]

Hi @mnikitina ,

Thank you. I ended up taking the lunch 2 variables and equation out and placing them into the 'else' part of the conditioner and it works now.

else
{
var startLunch2 = moment(fd.field('TimeOutLunch2').value);
var endLunch2 = moment(fd.field('TimeInLunch2').value);
diffLunch2 = endLunch2.workingDiff(startLunch2, 'hours', 'minutes');
fd.field('TotalTime').value = diff - (diffLunch1 + diffLunch2);
}

[lso]

Hi @mnikitina ,

I thought I had this working correctly but there is a freezing issue when I submit the form. After some testing I believe it is freezing because the time fields for 'TimeOutLunch2' and 'TimeInLunch2' can be a null value. When I use those fields to make the startLunch2 and endLunch2 variables it seems to be bugging with the moment calculations:
var startLunch2 = moment(fd.field('TimeOutLunch2').value);
var endLunch2 = moment(fd.field('TimeInLunch2').value);

After I choose to stop the freezing I can choose to edit the form because it is now saved somehow and I submit and it works fine without freezing. It's strange that it freezes on initial new form creations but when I edit these saved forms it doesn't freeze. Would you know a way around the null variable issue so it doesn't freeze after initial form creation?
Thank you

Current code as follows:

// Set default times on new time clock form creation
fd.spRendered(function() {
    var d = new Date();
    d.setHours(8, 30, 0);
    fd.field('TimeInWorkday').value = d;
});

fd.spRendered(function() {
    var d = new Date();
    d.setHours(17, 0, 0);
    fd.field('TimeOutWorkday').value = d;
});

fd.spRendered(function() {
    var d = new Date();
    d.setHours(12, 0, 0);
    fd.field('TimeOutLunch1').value = d;
});

fd.spRendered(function() {
    var d = new Date();
    d.setHours(12, 30, 0);
    fd.field('TimeInLunch1').value = d;
});


// Calculate total time worked in a day
requirejs.config({
    paths: {
        moment: "https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment-with-locales.min",
        'moment-business-time': "https://forms.plumsail.com/libs/moment-business-time"
    }
});

fd.spRendered(function() {

    require(['moment'], function(moment) {
        require(['moment-business-time'], function() {

            function calcDiff() {
                var startTime = moment(fd.field('TimeInWorkday').value);
                var endTime = moment(fd.field('TimeOutWorkday').value);
                var startLunch1 = moment(fd.field('TimeOutLunch1').value);
                var endLunch1 = moment(fd.field('TimeInLunch1').value);
                
                diff = endTime.workingDiff(startTime, 'hours', 'minutes');
                diffLunch1 = endLunch1.workingDiff(startLunch1, 'hours', 'minutes');
                
                    if (fd.field('TimeOutLunch2').value == null || fd.field('TimeInLunch2').value == null) 
                    {
                        fd.field('TotalTimeAsEdited').value = diff - diffLunch1;
                    } 
                    else 
                    {
                        var startLunch2 = moment(fd.field('TimeOutLunch2').value);
                        var endLunch2 = moment(fd.field('TimeInLunch2').value);
                        diffLunch2 = endLunch2.workingDiff(startLunch2, 'hours', 'minutes');
                        fd.field('TotalTimeAsEdited').value = diff - (diffLunch1 + diffLunch2);
                    }
            
                console.log(diff);
                console.log(diffLunch1);
                console.log(diffLunch2);
            }

            //Function that defines working hours
            function defineWorkHours () {

                moment.locale('en', {
                    workinghours: {

                        0: ['00:00:00', '23:59:59'],
                        1: ['00:00:00', '23:59:59'],
                        2: ['00:00:00', '23:59:59'],
                        3: ['00:00:00', '23:59:59'],
                        4: ['00:00:00', '23:59:59'],
                        5: ['00:00:00', '23:59:59'],
                        6: ['00:00:00', '23:59:59'],
                    }
                });
            }

            defineWorkHours ();

            // Calling function when the user changes times
            fd.field('TimeInWorkday').$on('change', calcDiff);
            fd.field('TimeOutWorkday').$on('change', calcDiff);
            fd.field('TimeOutLunch1').$on('change', calcDiff);
            fd.field('TimeInLunch1').$on('change', calcDiff);
            fd.field('TimeOutLunch2').$on('change', calcDiff);
            fd.field('TimeInLunch2').$on('change', calcDiff);

            // Calling function on form loading
            calcDiff(value, 'TotalTimeAsEdited');
        });
    });
});

[mnikitina]

Hello @lso,

Yes, the issue is related to the empty date fields. You can check if fields have values before running the code like this:

function calcDiff() {
    if(fd.field('TimeInWorkday').value && fd.field('TimeOutWorkday').value && fd.field('TimeOutLunch1').value && fd.field('TimeInLunch1').value) {
                var startTime = moment(fd.field('TimeInWorkday').value);
                var endTime = moment(fd.field('TimeOutWorkday').value);
                var startLunch1 = moment(fd.field('TimeOutLunch1').value);
                var endLunch1 = moment(fd.field('TimeInLunch1').value);
                
                diff = endTime.workingDiff(startTime, 'hours', 'minutes');
                diffLunch1 = endLunch1.workingDiff(startLunch1, 'hours', 'minutes');
                
                    if (fd.field('TimeOutLunch2').value == null || fd.field('TimeInLunch2').value == null) 
                    {
                        fd.field('TotalTimeAsEdited').value = diff - diffLunch1;
                    } 
                    else 
                    {
                        var startLunch2 = moment(fd.field('TimeOutLunch2').value);
                        var endLunch2 = moment(fd.field('TimeInLunch2').value);
                        diffLunch2 = endLunch2.workingDiff(startLunch2, 'hours', 'minutes');
                        fd.field('TotalTimeAsEdited').value = diff - (diffLunch1 + diffLunch2);
                    }
            
                console.log(diff);
                console.log(diffLunch1);
                console.log(diffLunch2);
            }
}