Freeze Timesheets Dynamically

Najmuddin Mohd · ‎06-22-2025

Recently, I got a requirement where I need to freeze timesheets every month, but with a twist: the deadline for regular users had to be different from the deadline for their managers.

This is a common but tricky challenge for any organization. A simple, static cutoff date like "the 28th of every month" is inflexible and causes problems:

What happens when the 28th falls on a weekend or a public holiday?
How do you fairly handle months of different lengths (28, 30, or 31 days)?
How do you give managers a few extra days to review and approve timesheets after their team members are locked out?

The goal was clear: we needed a robust, automated system that could calculate a "freeze date" based on working days from the end of the month, providing a fair and consistent cutoff period that required zero manual intervention each month.

The Solution: A Centralized Validation Engine

To solve this, I developed a Script Include called TimeSheetValidationUtils. This script acts as a central engine that handles all business logic for timesheet modifications. By using system properties for configuration, it remains flexible and easy to maintain without changing any code.

This knowledge article will break down our solution. I will walk through the core function, _isOperationPermitted, step-by-step to explain how it validates every timesheet change or deletion against this dynamic freeze policy.

High-Level Overview

The function processes a series of checks in a specific order. As soon as it finds a definitive reason to allow or deny the operation, it stops and returns a result. If a check doesn't apply, it moves to the next one.

The logic flow can be summarized as:

Is the user a "Super User" trying to make a change?
Is the time card for a locked previous month?
Is the time card for a future month?

If it's for the current month, has the "freeze date" passed?

Flow Summary Table

Step	Condition Checked	Result if True	Next Step if False
1	User is Admin/Super User AND operation is "Changes"?	Allowed. Function returns true.	Go to Step 2.
2	Time card's effective month is before the current month?	Disallowed. Function returns false with "locked" message.	Go to Step 3.
3	Time card's effective month is after the current month?	Allowed. Function returns true.	Go to Step 4.
4	Today's date is after the calculated Freeze Date?	Disallowed. Function returns false with "freeze date" message.	Allowed. Function returns true.

The Role of the Work Schedule

A critical component of this logic is the Work Schedule. The system doesn't just count calendar days to determine the freeze date; it intelligently counts working days.

What it is: The system is configured with a specific Work Schedule (defined by the timesheet.work_schedule_id system property). This schedule defines which days are considered business days (e.g., Monday-Friday) and which are non-working days (e.g., weekends and company holidays).
Why it's used: This ensures fairness and consistency. A policy like "users must submit time 3 working days before the end of the month" means the deadline is predictable, regardless of whether the month ends on a weekend or during a public holiday.
Example: If the last day of the month is a Sunday, and the freeze date is the 3rd-to-last working day, the system will count back from Friday, then Thursday, then Wednesday. The freeze date would be that Wednesday, correctly skipping the non-working weekend days.

Step-by-Step Processing Flow
The function _isOperationPermitted() takes two arguments: timeCardGr (the record of the time card) and operationType (a string, either "Changes" or "Deletion").

The very first check is to see if the user has special privileges.
- Logic: The system checks if the currently logged-in user has the admin role OR is a member of the Super User group (defined in the timesheet.superuser.group system property).
- Condition: This bypass only applies if the operationType is "Changes". Deletions are not bypassed by this rule.
- Result:
  - true: If the user is a Super User and the operation is "Changes", the function immediately returns { allowed: true, message: '' }. The process stops here.
  - false: If the user is not a Super User or the operation is "Deletion", the function proceeds to the next step.
The system needs to determine which month the time card belongs to. This is crucial for handling weeks that span two different months.
- Logic: The function calls _findFirstEntryDate(). This helper function scans the time card's daily hour columns to find the first day that has hours logged. The date of this first day becomes the "effective date" for the entire time card.
- Condition: If _findFirstEntryDate() returns null, it means the time card has 0 hours entered for all seven days.
- Result:
  - If the time card has hours logged, an "effective date" is established, and the function proceeds to the next step.
This step prevents any modifications to time cards from months that have already passed and are considered locked.
- Logic: The function compares the month and year of the time card's effectiveDate with the current system month and year.
- Condition: The check evaluates if the time card's effective date is in a month prior to the current one.
- Result:
  - false: If the time card is for a past month, the function returns { allowed: false, message: ... }. The process stops.
  - Error Message: "Changes are not allowed. This time card contains hours for a previous month (5/2023) which is now locked." (The operation type and date are dynamic).
This step handles time cards created for future periods.
- Logic: Similar to the step above, it compares the time card's effectiveDate with the current date.
- Condition: The check evaluates if the time card's effective date is in a month after the current one.
- Result:
  - true: If the time card is for a future month, modifications are always permitted. The function returns { allowed: true, message: '' }. The process stops.
If the script has reached this point, it has confirmed that the time card's effective date falls within the current calendar month. This is the final and most important check, which determines if the monthly cutoff "freeze date" has passed.
- Logic: The function delegates this check to the _checkAgainstFreezePolicy() helper function. This helper performs the following sub-steps:
  1. Calculate the Freeze Date: It calls _calculateFreezeDate(), which:
    - Determines if the current user is a regular User or the time card owner's Manager.
    - Reads system properties to find how many working days before month-end the freeze occurs (e.g., timesheet.freeze_days.user = 3, timesheet.freeze_days.manager = 1).
    - Uses the configured Work Schedule (as explained above) to count backwards from the last day of the month to find the Nth-to-last working day. This calculated date is the official freezeDate.
  2. Compare Today vs. Freeze Date: The _checkAgainstFreezePolicy() function then compares the current date with the calculated freezeDate.
- Condition: The check is effectively "is today's date after the freeze date?".
- Result:
  - false: If today's date is after the calculated freeze date, the period is locked. The function returns { allowed: false, message: ... }.
  - Error Message: "Deletion are not allowed. The freeze date for Users to modify this period's timesheets was 05/29/2023. Please contact your manager if deletion are required." (The operation, user type, date, and advice are all dynamic).
  - true: If today's date is on or before the freeze date, the operation is permitted. The function returns { allowed: true, message: '' }.

/**
 *  TimeSheetValidationUtils
 * @description Provides utility functions and business logic policies for time cards.
 * This includes validation for updates and deletions based on a configurable, dynamic freeze date.
 *
 * @dependency System Property [timesheet.work_schedule_id]: Sys_id of the work schedule.
 * @dependency System Property [timesheet.freeze_days.user]: Days before month end for user freeze.
 * @dependency System Property [timesheet.freeze_days.manager]: Days before month end for manager freeze.
 * @dependency System Property [timesheet.manager_override_enabled]: 'true' if managers have a different freeze date.
 * @dependency System Property [timesheet.test_date_override]: 'yyyy-MM-dd HH:mm:ss' to override "now" for testing.
 * @dependency System Property [timesheet.superuser.group]: 'sys_id' of the superUser group.
 */
var TimeSheetValidationUtils = Class.create();
TimeSheetValidationUtils.prototype = {

    initialize: function() {
        // Defines the daily hour fields in the order matching the GlideDateTime weekday index (Sunday = 0).
        this.dayFields = ['sunday', 'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday'];
    },

    /**
     * PUBLIC: Checks if a time card update is permitted.
     *  {GlideRecord} timeCardGr - The current time card record.
     * @returns {object} - An object {allowed: boolean, message: string}.
     */
    isUpdatePermitted: function(timeCardGr) {
        return this._isOperationPermitted(timeCardGr, "Changes");
    },

    /**
     * PUBLIC: Checks if a time card deletion is permitted.
     *  {GlideRecord} timeCardGr - The current time card record.
     * @returns {boolean} - True if allowed, false if not.
     */
    isDeletionPermitted: function(timeCardGr) {
        var result =  this._isOperationPermitted(timeCardGr, "Deletion");
        return result.allowed;
    },

    // --- PRIVATE HELPER FUNCTIONS ---

    /**
     * PRIVATE: Master policy check that handles all logic for any operation.
     *  {GlideRecord} timeCardGr - The current time card record.
     *  {string} operationType - The operation being performed ("Changes" or "Deletion").
     * @returns {object} - An object {allowed: boolean, message: string}.
     */
    _isOperationPermitted: function(timeCardGr, operationType) {
        var currentUser = gs.getUser();
        var superUserGroupId = gs.getProperty("timesheet.superuser.group");
        var isSuperUser = currentUser.isMemberOf(superUserGroupId) || currentUser.hasRole("admin");

        if (operationType == "Changes" && isSuperUser) {
            return { allowed: true, message: '' };
        }

        var effectiveDate = this._findFirstEntryDate(timeCardGr);
        if (!effectiveDate) {
            // No hours entered, so any operation is allowed.
            return { allowed: true, message: '' };
        }

        var now = this._getCurrentDateTime();
        var currentYear = now.getYearUTC();
        var currentMonth = now.getMonthUTC();
        var recordYear = effectiveDate.getYearUTC();
        var recordMonth = effectiveDate.getMonthUTC();

        // Check 1: Is the time card for a past, locked month?
        if (recordYear < currentYear || (recordYear == currentYear && recordMonth < currentMonth)) {
            var pastMonthMessage = operationType + " are not allowed. This time card is for a previous month (" + recordMonth + '/' + recordYear + ') which is now locked.';
            return { allowed: false, message: pastMonthMessage };
        }

        // Check 2: Is the time card for a future month?
        if (recordYear > currentYear || (recordYear == currentYear && recordMonth > currentMonth)) {
            return { allowed: true, message: '' };
        }

        // If it's for the current month, check against the freeze date policy.
        return this._checkAgainstFreezePolicy(timeCardGr, operationType, effectiveDate);
    },

    /**
     * PRIVATE: Gets the "current" date. Reads a system property for a test override.
     * @returns {GlideDateTime} The real or overridden current GlideDateTime.
     */
    _getCurrentDateTime: function() {
        var testDateStr = gs.getProperty('timesheet.test_date_override');
        if (!testDateStr) {
            return new GlideDateTime();
        }
        var testGdt = new GlideDateTime(testDateStr);
        if (testGdt.isValid()) {
            gs.addInfoMessage("TimeSheetValidationUtils: Date override is ACTIVE. Using test date: " + testGdt.getDisplayValue());
            return testGdt;
        }
        return new GlideDateTime();
    },

    /**
     * PRIVATE: Determines the "effective date" of a time card by finding the first day
     * in the week that has hours logged.
     *  {GlideRecord} timeCardGr - The time card record.
     * @returns {GlideDateTime | null} - The date of the first logged hour, or null if no hours are logged.
     */
    _findFirstEntryDate: function(timeCardGr) {
        var weekStartDate = new GlideDateTime(timeCardGr.week_starts_on);
        for (var i = 0; i < this.dayFields.length; i++) {
            var dayField = this.dayFields[i];
            if (parseFloat(timeCardGr.getValue(dayField) || 0) > 0) {
                var effectiveDate = new GlideDateTime(weekStartDate);
                effectiveDate.addDays(i);
                return effectiveDate;
            }
        }
        return null; // No hours found
    },

    /**
     * PRIVATE: Checks if the operation is allowed based on the freeze date for the effective month.
     *  {GlideRecord} timeCardGr - The time card record.
     *  {string} operationType - The type of operation (e.g., "Changes", "Deletion").
     *  {GlideDateTime} effectiveDate - The date determining the month for the freeze calculation.
     * @returns {object} - An object {allowed: boolean, message: string}.
     */
    _checkAgainstFreezePolicy: function(timeCardGr, operationType, effectiveDate) {
        var freezeDateResult = this._calculateFreezeDate(timeCardGr, effectiveDate);
        if (!freezeDateResult.date) {
            return { allowed: false, message: freezeDateResult.message };
        }

        var today = this._getCurrentDateTime();
        if (today.getLocalDate().after(freezeDateResult.date.getLocalDate())) {
            var message = operationType + " are not allowed. The freeze date for " + freezeDateResult.actorType + "s was " + freezeDateResult.date.getDisplayValue() + ".";
            if (freezeDateResult.actorType === 'User') {
                message += " Please contact your manager if " + operationType.toLowerCase() + " are required.";
            }
            return { allowed: false, message: message };
        }

        return { allowed: true, message: '' };
    },

    /**
     * PRIVATE: Calculates the freeze date for a given month based on user type and schedule.
     *  {GlideRecord} timeCardGr - The time card record.
     *  {GlideDateTime} dateWithinTargetMonth - A date within the month we need the freeze date for.
     * @returns {object} - An object {date: GlideDateTime, actorType: string, message: string}.
     */
    _calculateFreezeDate: function(timeCardGr, dateWithinTargetMonth) {
        var scheduleId = gs.getProperty("timesheet.work_schedule_id");
        if (!scheduleId) {
            return { date: null, actorType: null, message: "Configuration error: Timesheet work schedule is not defined." };
        }

        var schedule = new GlideSchedule(scheduleId);
        if (!schedule.isValid()) {
            return { date: null, actorType: null, message: "Configuration error: The defined timesheet schedule is invalid." };
        }

        var userFreezeDays = parseInt(gs.getProperty("timesheet.freeze_days.user", "3"));
        var managerFreezeDays = parseInt(gs.getProperty("timesheet.freeze_days.manager", "1"));
        var managerOverrideEnabled = gs.getProperty("timesheet.manager_override_enabled", "true") == "true";

        var isManager = (managerOverrideEnabled && timeCardGr.user.manager == gs.getUserID());
        var effectiveFreezeDays = isManager ? managerFreezeDays : userFreezeDays;
        var actorType = isManager ? "Manager" : "User";

        var freezeDate = this._findNthLastWorkingDayOfMonth(schedule, effectiveFreezeDays, dateWithinTargetMonth);
        if (!freezeDate) {
            return { date: null, actorType: actorType, message: "Could not determine the timesheet freeze date for this period." };
        }

        return { date: freezeDate, actorType: actorType, message: '' };
    },

    /**
     * PRIVATE: Finds the Nth working day by counting backwards from the end of a given month.
     *  {GlideSchedule} schedule - The schedule defining working days.
     *  {number} targetWorkingDayFromEnd - The Nth working day to find (e.g., 3 for the 3rd to last).
     *  {GlideDateTime} dateWithinTargetMonth - A date within the month to be calculated.
     * @returns {GlideDateTime | null}
     */
    _findNthLastWorkingDayOfMonth: function(schedule, targetWorkingDayFromEnd, dateWithinTargetMonth) {
        var dateIterator = new GlideDateTime(dateWithinTargetMonth);
        dateIterator.setDayOfMonthUTC(dateIterator.getDaysInMonthUTC()); // Start at the last day of the month
        var workingDaysCounted = 0;

        // Iterate backwards from the end of the month
        for (var i = 0; i < dateIterator.getDaysInMonthUTC(); i++) {
            if (schedule.isInSchedule(dateIterator)) {
                workingDaysCounted++;
                if (workingDaysCounted >= targetWorkingDayFromEnd) {
                    return dateIterator; // Found the target Nth working day
                }
            }
            dateIterator.addDaysUTC(-1);
        }
        return null; // Should not happen in a valid month
    },

    type: 'TimeSheetValidationUtils'
};

The above script include function isUpdatePermitted() was called from Before Update Business rule on the TimeCard table.

If the above information helps you, Kindly mark it as Helpful.
Regards,
Najmuddin.

Freeze Timesheets Dynamically

The Solution: A Centralized Validation Engine

High-Level Overview

Flow Summary Table

The Role of the Work Schedule

Step-by-Step Processing Flow