Creator Simplified #8: Calculate business hours between date-time fields in Zoho Creator

Creator Simplified #8: Calculate business hours between date-time fields in 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!
 

    • Recent Topics

    • Kaizen #194 : Trigger Client Script via Custom buttons

      Hello everyone! Welcome back to another interesting and useful Kaizen post. We know that Client Scripts can be triggered with Canvas buttons and we discussed this with a use case in Kaizen#180. Today, let us discuss how to trigger Client Script when a

    • [Webinar] A recap of Zoho Writer in 2025

      Hi Zoho Writer users, We're excited to announce Zoho Writer's webinar for January 2026: A recap of Zoho Writer in 2025. This webinar will provide a recap of the features and enhancements we added in 2025 to enhance your productivity. Choose your preferred

    • Picklist field shows "none" as default

      Hello, Is there an option to avoid showing "none" as the default value in a picklist field? I also don't want to see any option displayed. My expectation is to have a blank bar, and then when I display the drop-down list, I can choose whichever I wa

    • Stage-probability mapping feature in custom module

      Hi, I'm building a custom module for manage projects. I would like to implement the stage-probability feature that Potentials has. Is this possible?

    • Field Description is very small

      Hello, The field Description in the activity is very small. Why don't try open a new window, or a bigger popup, or increase the width of the "popup". Example:

    • StatusIQ

      Please add StatusIQ to data sources. We using site24x7 and StatusIQ together and site24x7 integration is already there. Thanks and regards, Torsten

    • In Zoho People, the Operations buttons are frequently not visible or do not appear consistently.

      In Zoho People, the Operations buttons are frequently not visible or do not appear consistently. We request you to please investigate and address this issue, as it is affecting daily HR operations and user access.

    • Marketing Tip #14: Increase cart value with product bundles

      Bundling products is a great way to increase average order value while giving customers more convenience. Think “camera + tripod + memory card” or “soap + lotion + bath salts.” Bundles make shopping easier and feel like a better deal. It’s a win-win for

    • Problem with Workdrive folders

      I'm having a problem a problem accessing files in a Zoho work drive folder when using the Zoho writer app. The problem folder appears grayed out in the Zoho work drive window in both the online and writer application. However I can open the folder in

    • Add RTL and Hebrew Support for Candidate Portal (and Other Zoho Recruit Portals)

      Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to set the Candidate Portal to be Right-to-Left (RTL) and in Hebrew, similar to the existing functionality for the Career Site. Currently, when we set the Career Site

    • Auto tracking URL generation based on Carrier

      Hi, While creating a shipment order for a package in Zoho Books, I have a requirement that for example, if the carrier is Delhivery and tracking number is 1234, then can automatically the tracking link/URL be generated as www.delhivery.com/1234. Similary,

    • Zoho Bookings form pre-filled with Zoho Forms in

      Hi, I've got a contact page on my website and I'd like to have the option to book an appointment (redirected to zoho bookings page) after an option is submitted on the contact form. how would I go about doing this? thanks

    • Pre-orders at Zoho Commerce

      We plan to have regular producs that are avaliable for purchase now and we plan to have products that will be avaliable in 2-4 weeks. How we can take the pre-orders for these products? We need to take the money for the product now, but the delivery will

    • Customer ticket creation via Microsoft Teams

      Hi all, I'm looking to see if someone could point me in the right direction. I'd love to make it so my customers/ end users can make tickets, see responses and respond within microsoft teams. As Admin and an Agent i've installed the zoho assist app within

    • Zoho Books' 2025 Wrapped

      Before we turn the page to a new year, it’s time to revisit the updates that made financial management simpler and more intuitive. This annual roundup brings together the most impactful features and enhancements we delivered in 2025, offering a clear

    • Can multiple agents be assigned to one ticket on purpose?

      Is it possible to assign one ticket to two or more agents at a time? I would like the option to have multiple people working on one ticket so that the same ticket is viewable for those agents on their list of pending tickets. Is something like this currently

    • Edit default "We are here to help you" text in chat SalesIQ widget

      Does anyone know how this text can be edited? I can't find it anywhere in settings. Thanks!

    • Can't change form's original name in URL

      Hi all, I have been duplicating + editing forms for jobs regarding the same department to maintain formatting + styling. The issue I've not run into is because I've duplicated it from an existing form, the URL doesn't seem to want to update with the new

    • Feature Request: Sync Leave Tracker to Zoho Mail / Calendar or provide option to download information

      Zoho Leave Tracker offers the option to sync the leave Calendar to Microsoft 365 and Google Calendar. Adding an option to sync to Zoho-Mail Calendar would avoid duplication and add significant value for users. An alternative would be to allow users to

    • Compensation | Salary Packages - Hourly Wage Needed

      The US Bureau of Labor Statistics says 55.7% of all workers in the US are paid by the hour. I don't know how that compares to the rest of the world, but I would think that this alone would justify the need for having an hourly-based salary package option.

    • Multiple currencies - doesn’t seem to work for site visitors / customers

      I am trying to understand how the multiple currency feature works from the perspective of the website visitor who is shopping on my Zoho Commerce site. My site’s base currency is US Dollars (USD) but my store is for customers in Costa Rica and I would

    • Archiving Contacts

      How do I archive a list of contacts, or individual contacts?

    • How do people handle using Outlook and Zoho Project calendar at the same time?

      We have an ongoing problem in our organisation where we use Zoho Projects to plan all of our projects tasks and that also allows us to look forward using the workload report to see which of our consultants are overstretched etc and which are available.

    • 10GB Email Storage Limits in Zoho CRM

      We’ve had Zoho One for almost 5 years and have always synced our emails from Gmail via IMAP… As of late, we’ve run into issues with our emails not syncing, due to being over the 10GB storage cap… What’s very odd is that we haven’t changed a thing? I know

    • Performance is degrading

      We have used Mail and Cliq for about three years now. I used to use both on the browser. Both have, over the past 6 months, had a severe degradation in performance. I switched to desktop email, which appeared to improve things somewhat, although initial

    • How to set page defaults in zoho writer?

      hi, everytime i open the zoho writer i have to change the default page settings to - A4 from letter, margins to narrow and header and footer to 0. I cannot set this as default as that option is grayed out! so I am unable to click it. I saved the document

    • Unable to Send Different Email Templates for Different Documents in Zoho Sign

      Hello Zoho Community, I am facing a limitation with Zoho Sign regarding email notifications sent to customers when a document is sent for signing. Currently, whenever I send any template/document for signing, the email notification that goes to the customer

    • Enable History Tracking for Picklist Values Not Available

      When I create a custom picklist field in Deals, the "Enable History Tracking for Picklist Values" option is not available in the Edit Properties area of the picklist. When I create a picklist in any other Module, that option is available. Is there a specific reason why this isn't available for fields in the Deals Module?

    • ZO25: The refreshed, more unified, and intelligent OS for business

      Hello all, Greetings from Zoho One! 2025 has been a remarkable year, packed with new features that will take your Zoho One experience to the next level! From sleek, customizable dashboards to an all-new action panel for instant task management, we’ve

    • Vault crashes on Android Devices

      Vault is continuously closing after entering the master password on my Android device. After several attempts I get a system message that says there is a bug in the app. I've uninstalled and reinstalled the app, and cleared the app cache, but nothing

    • ¿Cómo puedo configurar las contraseñas creadas bajo una directiva para que nunca caduquen y no aparezcan como caducadas en los informes?

      ¿Cómo puedo configurar las contraseñas creadas bajo una directiva para que nunca caduquen y no aparezcan como caducadas en los informes? La razón por la cual contraseña estas no deben caducar es porque su actualización depende de mi cliente y no de mí.

    • Camera access

      My picture doesn't appear in a group discussion. (The audio is fine.) The guide says "Click the lock icon on address bar," but I can't find it. Advise, please

    • Are static links available

      I'm still using Zoho Meeting in trial mode. My previous webinar software provided a static link, and I made the mistaken assumption that I could send out my link and start a meeting later. Mass confusion, but my fault. With a paid version do you get a

    • Why is Zoho Meeting quality so poor?

      I've just moved from Office 365 to Zoho Workplace and have been generally really positive about the new platform -- nicely integrated, nice GUI, good and easy-to-understand control and customisation, and at a reasonable price. However, what is going on

    • How to print a label from zoho creator app?

      Hello, I would like to print a label from zoho creator app record similar to attached one. Size 74mm x 102mm. I tried record template. It leaves plenty of space around the content and also I couldn't set the height of the page. So it is not printing properly. Could someone please direct me to right direction for this requirement?

    • Integration between "Zoho Sprints Stories" and "Zoho Projects Tasks/Subtasks"

      We have two separate teams in our organization using Zoho for project management: The Development team uses Zoho Sprints and follows Agile/Scrum methodology. The Infrastructure team uses Zoho Projects for traditional task-based project management. In

    • Where can we specify custom CSS in Zoho Forms custom theme ?

      I'm using a form with a dark theme. The OTP popup window is unreadable, because for some reason, the OTP popup background fixes color to white, but still takes the font color specified in the custom theme. This ends up as white on white for me, rendering

    • Team Gamification

      Would love to motivate, engage and encourage our team with our social media posts. Would like to include Gamification features of Social Media in Zoho Social or Marketing Automation. And also bring in Social Advocacy tools/tracking/management to these,

    • Sync Data from MA to CRM

      Currently, it's a one-way sync of data from the CRM to MA. I believe we should have the ability to select fields to sync from MA to the CRM. The lead score is a perfect example of this. In an ideal world we would be able to impact the lead score of a

    • Update CRM record action

      Currently, MA only offers a "Push Data" action to push data to a CRM module. This action is built to cover the need to both create a new record and update an existing record. Because it has been implemented this way all required fields on the CRM module

    • Next Page