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!
 

    • Recent Topics

    • Bigin iOS, macOS and Android app update - File Cabinet

      Hello Everyone! In the most recent iOS (v1.7.1) and macOS (v1.5.1) updates of the Bigin app, we have included support for the File Cabinet functionality. You can access this feature as a topping within the web app (bigin.zoho.com) and conveniently use

    • Power of Automation :: Automatically close the associated tasks once the Issues are closed

      Hello Everyone, A Custom Function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as to when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. We have

    • Meeting vs Zoom and why we are moving

      Dear Meeting team,                                   I want to share my thoughts on Meeting vs Zoom and explain why we are moving back to Zoom for all our video conferencing, even though it is part of our Zoho One Subscription. 1). Video & Audio quality - We see a significant downgrade in video quality when using Meeting vs Zoom, even when using the same internet connections.  Meeting video is blurry and not sharp compared to Zoom.  Audio is also hit and miss on Meeting with frequent drop outs and

    • Holidays and celebrations: Unveiling SalesIQ's seasonal theme!

      The holiday season is upon us, and we are feeling all the more merrier at SalesIQ while we unveil this seasonal update. Get ready to sprinkle some holiday magic onto your websites with SalesIQ's new seasonal theme. This new festive theme will provide

    • booking link that expires

      I have a suggestion that is  crucial. When i send booking URL to clients they keep the link and they book appointment whenever they want multiple times. You should give us the Booking URL feature. We should be able to send it and the user can use it only

    • Print record summary to pdf page breaks

      Hello. I created a record summary and would like to export to pdf. Rich text fields or photos can get printed on 2 pages cutting them in half. Is there a way to add page breaks? Thanks.

    • The email address you have entered belongs to a different deployment/region.

      Hi, I am trying to create the user - mprust@crombiecomputers.co.uk but keep getting the message below -  The email address you have entered belongs to a different deployment/region. Please contact support@zohoaccounts.com for assistance. Look forward

    • Pre-fill TO and CC fields for Email Templates

      This would be a game changer to be able to set either specific email addresses or merge fields based on deal role titles into email templates. Please pass this along to *hopefully* add to future features of Zoho CRM.

    • Leads and Deals Issue

      Hi! Is Zoho having trouble today? I've been experiencing issues today. When I move a deal to closed/won, it previously would require a closing date but today the screen just went blur and the closing date did not pop up. And then under leads, I was trying

    • How do I hide all leads from Standard users in my CRM ZOHO

      I want to configure my crm zoho in such a way that standard users will only see leads owned or assigned to them. How do I go about doing this? I have tried all i could, but nothing...can anyone help me?

    • How to Implement Time-Based Filters in Zoho Workflows?

      Hi everyone, I’m looking for a workaround to implement time-based filters in Zoho workflows. Specifically, I need a workflow to trigger only within certain hours (e.g., between 10:00 AM and 8:00 PM, Sunday to Friday). Currently, Zoho workflows execute

    • is there a better way to routinely export my project timesheets?

      Hi there, I am somewhat happy with how Zoho books handles time.  One thing that is missing is more user driven control over my data.  I have a workflow where I constantly need to give customers feeds of work done.  So I pick a project, and would ideally

    • Portal Default View

      Hello! My company is about roll out portals for some users, but we were wondering if it was possible to set the Record Detail Page View to default to a custom view that we made. That way the information they need would be streamlined and easier to see.

    • Does zoho rest api support token exchange from microsoft entra id token

      i want to get access token from the Microsoft user token in rest api. so it is possible to exchange microsoft user access token to zoho's access token or auth token ( token should be user specific not super admin).

    • Important: DKIM verification needed for unauthenticated email domains

      Hi all, This post is to inform users who are currently using unauthenticated email domains as their sender email for their notifications. We have mandated DKIM verification for all domains used as sender email addresses for the outgoing emails by 01-Feb-2025

    • Zoho Sign Product updates - H2 2024

      Hello! We have almost come to the end of 2024! Here's a list of features and enhancements that went live in the later half of the year. NOM 151 certification Witness signing Formula, conditional, and custom fields Zoho Sign's extension for Bigin by Zoho

    • Custom Status for Purchase Orders

      Currently Zoho books has functionality to create custom statuses for Sales Orders. Can this be extended to include custom status for purchase orders as well? It was a great decision to add this functionality to sales orders. Our use case is for tracking

    • Remove Address from credit card payment

      I would like to remove the need to add address when paying by credit card. I only want the customer to have to add their credit card details.

    • New Built In QR/Barcode Generator Print Settings

      I'm trying out the new QR/Barcode generator field in Creator. I would think most people will want to print these, like I do. I am not seeing any way to control the height or width of the barcode for printing (inside the print/pdf template builder). The

    • My Zoho mail has been blocked for suspicious activity. cannot unblock it! need urgent help!

      Hi, I was accessing my email on an airplane which for some reason resulted in my mail being blocked. I have managed to unblock my incoming however outgoing is still blocked. I have contacted all your support emails but it still says contact support when I try to unblock outgoing emails. Its now been over 8 days with the same issue which is really serious as I'm not able to send several business emails during this period. I have followed all your steps to unblock this and contacted your support 5

    • Copy Widget to another Dashboard

      I can see the option to clone a widget to the same dashboard but is it possible to copy it to another dashboard?

    • Error while importing products Purchase Price: Value in the field does not match with the data type supported by us.

      I am trying to import items into zoho inventory using an excel spreadsheet. I keep getting this error Purchase Price: Value in the field does not match with the data type supported by us. no matter how i format the cells this comes up and will not import

    • Quotes Module - import data

      Hello Zoho, is it possible to import Quotes records? I was trying and i have no results. Raport shows no data imported. Could you help me please how to do it?

    • Year-End Wrap: Disconnect now; Reconnect later with Offline Mode

      🎄Happy Holidays🎄 Let's say you are travelling home to spend the holiday season with your loved ones. Before you even board the train, you check your phone only to find your inbox rapidly filling with urgent emails that need your attention. There’s no

    • Resource booking functionality questions

      I'm exploring the resource booking functionality in Zoho Bookings for my organisation's needs. I have a few questions about the available Zoho Bookings functionalities. Is it possible to force all users to sign up for an account before they book a resource?

    • How to create a Field with answers as Yes, No> Further if no is selected a new field to be visible to give details

      Dear All, I am creating a feedback form in HR Letter. The question is were you satisfied with the work allotted. Expected answer to this is Yes, No. Further if the response is no, then a field to be give to fill more details as to why no was selected.

    • Modify the way a phone number is shown in footer on ZOHO Booking Page

      The default display of the phone number field in the footer is not customer friendly - See image below. We would like it to use the accepted standards of phone number display: +61 (0)2 88545440, or allow us to choose. At the moment ZOHO Booking sets how

    • Zoho Bookings Multilingual ?

      Hello, Not sure if there is a way to do it currently, but it would be very helpful if Zoho bookings allowed multilingual translations so we could have our booking pages and notifications setup in multiple languages. Thanks,

    • Allow customers to choose meeting venue and meeting duration on booking page

      My business primarily involves one-to-one meetings with my clients. Given the hybrid-work world we now find ourselves in, these meetings can take several forms (which I think of as the meeting "venue"): In-person Zoom Phone call I currently handle these

    • Two way sync Zoho Mail and Bookings

      Hi, I know it’s possible to view Bookings appointments in Zoho Mail, but is there a way to see my Zoho Mail meetings in the Bookings calendar?

    • Modifying iframe data of Zoho booking iframe

      Hello, I have integrated a Zoho Bookings embedded iframe into my website. Currently, I am pre-filling the booking form with default values as part of our process flow. However, I want to ensure that if an input field is already populated with a default

    • Sending my emails to Spam Folder

      I am loosing my business because of this issue, many of my customers are receiving their email in the spam sometimes no one checks them

    • Why can't I sync my Zoho Mail tasks and notes with my mobile device, and is there a way to fix this issue?

      I'm having trouble syncing my Zoho Mail tasks and notes with my mobile device. Despite following the usual steps, the tasks and notes don't appear on my phone. Is there a way to fix this issue? Regards, Adamjes, Tekskills India Pvt Limited, India.

    • Re: constant rejected email form and to my account

      Hi, have had several rejection when I sent an email to Zoho account as well as other people have sent emails to my as your health Centre they have account and all aspects have been ejected. i wonder why the email are rejecting to and from. If you see

    • How do I redeem credits?

      How do I redeem credits that are shown in billing section?

    • Cannot add IMAP account to "new" Outlook

      Hi, I am attempting to add an IMAP account to my copy of the "new" Outlook on my desktop computer. I have tried using the default password when MFA was disabled and an app password when MFA was enabled. But neither attempt worked. My Windows 11 OS number

    • Update Zoho Flow on Sprint Work Item Status Change

      Hello, I've contacted Zoho One support but have been unable to help in a timely manner, so I'm asking the community. I want to start using sprints, but I'm having an issue. I need to post updates to Slack when a Work Item has a status change. My understanding

    • Create Dashboard using data/tables from different workspaces

      Is it possible do create a Dashboard using different tables that are part of different Workspaces?

    • Este domínio já está associado a esta conta

      quando digitei meu domínio recebi essa mensagem que meu domínio estava associado a uma conta que eu nem faço idéia de quem seja. Como que faço pra resolver isso?

    • Storage addon failed to upgrade

      Hi, I am trying to purchase addon storage, but i am getting upgrade failed error Sorry! We are unable to process your information. Please try again after some time. If the problem occurs again, please visit our Contact Us page to reach out to the appropriate

    • Next Page