Calculate business hours between two date fields| Zoho Creator

Calculate business hours between two date fields| Zoho Creator

Hey Creators!

It's time to learn something new and simplify your app development journey with Zoho Creator. 

Requirement

Calculate business hours between two chosen date-time inputs.

Sample Use-case

In a Project Tracker application, the team lead selects the start and end dates while assigning tasks to team members, and the business hours are automatically calculated based on the shift hours.

Logic

  1. Create a form (here, Add Task). Add two date-time fields and a number field to populate the business hours.




  2. Configure a form workflow on user input of the End_Date_Time field and copy the code shown below. For ease of understanding, the code is split into four parts, followed by their explanation.

  1. //Declare variables for start and end date-time inputs. 

  2.      startDateTime = input.Start_Date_Time;

  3.      endDateTime = input.End_Date_Time;


  5. //Declare shift details 

  6.     shiftStartTime = "09:30:00 AM";

  7.     shiftEndTime = "06:30:00 PM";

  8.     shiftDurationInHours = 9;


  10. //Defining calendar 

  11.     weekends = {"Saturday", "Sunday"};

  12.     holidays = {};

Here, we declare variables by the names startDateTime and endDateTime for the start and end date-time inputs, respectively. Next, we define the shift timings and shift duration of a day in hours. Then, define all the non-working days. Here, we're only considering weekends, i.e., Saturday and Sunday.

  1.  //Declarations
  2. shiftEndOnStartDate = toDateTime(startDateTime.tostring("dd-MMM-yyyy") + " " + shiftEndTime,"dd-MMM-yyyy hh:mm:ss a");
  3. shiftStartOnEndDate = toDateTime(endDateTime.tostring("dd-MMM-yyyy") + " " + shiftStartTime,"dd-MMM-yyyy hh:mm:ss a");


Next, we declare variables for the time when the shift ends on the starting day and when the shift begins on the ending day, respectively.

Here, shiftEndOnStartDate is created by combining the date part of StartDateTime and the time part of ShiftEndTime to get the shift end time of the starting day. To elaborate,  it takes the start date and converts it into a string (startDateTime.tostring("dd-MMM-yyyy") ) and adds the previously defined shiftEndTime in the dd-MMM-YYY hh:mm:ss format. For instance, let's say the StartDateTime is 17-NOV-2024 at 12:30:00 PM, and the shift ends at 06:30:00 PM; the shiftEndOnStartDate will be 17-NOV-2024 at 06:30:00 PM.

Similarly, we calculate the date-time when the shift starts on the ending day and store it in the variable shiftStartOnEndDate.

Next, to calculate the business hours, we calculate the available hours on the start day with respect to the time chosen. 
  1. //Hours left on the start date
  2. hoursLeftOnStartDate = startDateTime.timeBetween(shiftEndOnStartDate);
  3. hoursLeftOnStartDateString = hoursLeftOnStartDate.toTime();
  4. hoursLeftOnStartDateInSeconds = hoursLeftOnStartDate.getprefix(":").toNumber() * 3600 + hoursLeftOnStartDate.getsuffix(":").getprefix(":").toNumber() * 60;
  5. hoursLeftOnStartDateInHours = hoursLeftOnStartDateInSeconds / 3600;

  7. //Hours left on the end date
  8. hoursLeftOnEndDate = shiftStartOnEndDate.timeBetween(endDateTime);
  9. hoursLeftOnEndDateInSeconds = hoursLeftOnEndDate.getprefix(":").toNumber() * 3600 + hoursLeftOnEndDate.getsuffix(":").getprefix(":").toNumber() * 60;
  10. hoursLeftOnEndDateInHours = hoursLeftOnEndDateInSeconds / 3600;

startDateTime.timeBetween(shiftEndOnStartDate) calculates the difference between the chosen date-time and the shift ending time on the same day.

toTime() returns the hoursLeftOnStartDate value in the date-time format specified in the application settings.

Then, we convert this time into seconds.

(A) hoursLeftOnStartDate.getprefix(":").toNumber() * 3600 takes the hours part, converts it to a number, and multiplies it by 3600 to get the time in seconds.

(B) hoursLeftOnStartDate.getsuffix(":").getprefix(":").toNumber() * 60 takes the minutes part, converts it to a number, and multiplies by 60 to get the time in seconds.

Both (A) and (B) are added to get the business time available on the starting day in seconds.

This value is later converted to hours and stored in the variable hoursLeftOnStartDateInHours.

The same process is repeated to calculate the business hours on the end date.


Next, we need to calculate the number of workdays between startDateTime and endDateTime.
  2. num_work_days = startDateTime.workDaysBetween(endDateTime,weekends,holidays);

  4. //Subtracting 1 to exclude the end date. The start date is excluded by default.
  5. total_business_hours = (num_work_days - 1) * shiftDurationInHours + hoursLeftOnStartDateInHours + hoursLeftOnEndDateInHours;

  7. input.Business_days = total_business_hours.round(1);

num_work_days returns the no.of working days between the starting day StartDateTime and EndDateTime, excluding the weekends and holidays declared earlier.

The workDaysBetween function excludes the start day by default. We also subtract one digit from the number of working days to exclude the end date.

Finally, to calculate the total_business_hours, multiply the number of working days between two given inputs (num_work_days - 1) by the shift hours (shiftDurationInHours) and add the remaining business time on the start date (hoursLeftOnStartDateInHours) and on the end date (hoursLeftOnEndDateInHours).

total_business_hours.round(1) returns the rounded-off value of the total business hours and populates it in the Business_Hours field.

Output




That's all for this post.

Feel free to share your thoughts, queries, and ideas in the comment section below.

Thank you!