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

    • Calendar View for Zoho Tickets

      Is there a way to view your tickets with due dates on a calendar view? I can not find a way to merge my Zoho Calendar and Tickets. This would be extremely helpful to my team.

    • Delete / Modify Default Career Site - Zoho Recruit

      Hello, It would be very useful if we could delete a default career site or change which of our career site is the default. Our Career site was created when there were issues with Zoho Recruit creating English CTA buttons on French Career sites. The only

    • Is it possible to hide Developer Space for all user in Zoho Projects

      Hello! I am Zoho admin in a company and we want to use Zoho Project to manage projects, but after a few days of testing we are not able to "hide" the Developer Space from all kind of users except the admin. To sum up, I want to hide this for all users.

    • Introducing automation and utility conversations in WhatsApp marketing

      We’re excited to announce the addition of two new features to our WhatsApp integration: Automation and Utility conversations. These enhancements will allow you to streamline your marketing efforts and engage with your customers more effectively by automating

    • Você já viu os cursos do Zoho Mind?

      Pessoal, Tem uma plataforma da Zoho chamada Zoho Mind, muito interessante os cursos e vídeos tutoriais que lá possui. Para a turma do Zoho Creator, tem uma dica de Buscar dados em Formulário, segue o link e clique em Zoho Creator. https://www.zohomind.com.br/#/videostutoriais

    • Como gerar gatilhos para pagamento de impostos no Zoho Books?

      Olá Pessoal, boa tarde! Gostaria de saber como vocês estão escriturando os impostos a pagar no Zoho Books. Vi que temos a opção de Bills, porém se eu escriturar nesta aba do Zoho Books para gerar lembretes de tempo de vencimento por exemplo vai refletir

    • Subform Time field to string.

      Good afternoon All. I have a Subform 'Delivery_Receiving_Hours' that captures Day (Dropdown), Time_Open (Time), and Time_Close (Time). I need to capture this data and send it to a multiline field in the CRM. The code, posted below, below will capture

    • Data export

      I need to export our customer's data and projects' data for our purpose but am unable to export full data i only get around 3160 projects and around 2k customer can you please help me to get full data, please

    • Zoho Marketing Plus : Un outil tout-en-un pour la création de pages, la collaboration et la gestion du calendrier marketing

      Nous sommes ravis de vous présenter trois nouvelles fonctionnalités puissantes de Zoho Marketing Plus s’enrichit désormais d’un page web (l'éditeur de pages), qui vous permet de créer des pages attrayantes et à fort taux de conversion pour vos campagnes

    • Grouping payments to match deposits

      Is there a way to group multiple invoice payments together so they match credit card batches and grouped deposits in the bank account? Basically, we are creating invoices for each of our transactions, and applying a payment to each of the invoices. Our payments are either credit cards or checks. We want to be able to group payments together so when our bank account reflects a credit card batch made up of many transactions, or the deposit we took to the bank that has multiple checks from different

    • Employees can not add some expenses suddenly

      Zoho expense was working fine and whenever there was a new merchant, it would automatically add and also the same auto added in Zoho Books (due to merchant-vendor sync) untill now. From today, it is having problems in searching the existing vendors and

    • iOS 10: Caller ID new feature?

      Hi, in the update history of the iOS App (for iOS10) - v.3.2 - i found the point "caller identification" has this feature been deactivated again? i cannot find anything on my iphone on how to activate this feature. or does it just work from the beginning?

    • Recommendations to store meeting notes for easy access from Contacts, Accounts & Deals module records?

      I would like your advice on how to achieve this use case for my organization. It’s related to where/how best to store meeting notes from a conversation with Contact(s) working at an Account (Company) in the context of a Deal. The ideal solution (from

    • Bank reconciliation. Match Transaction -filter

      When matching an imported bank statement file we only get a match if it is an excact match on both amount and date. Then a suggestions comes up with a very broad selection regarding amount, and no default "between" dates. I can then go an manually adjust the filter, and have to put in from-to amounts and dates. How do I set a default from-to date?  As an example, I would like the date to be +- 3 days, Thanks.

    • Added new staff but does not appear in other organization list

      Hi, I added the new staff under Sales Manager in the contacts, but it does not appear in the other organization list where I need to create a contact, and I can't select the newly added Sales Manager

    • Integrating Calendly with Zoho Calendar in Zoho Mail

      I moved my office into a business incubator space that uses Calendly for meeting management and events. Calendly doesn't have a integration with Zoho Calendar and vice versa. I was directed to Zapier for integration but it doesn't have an integration

    • Map fields from module X to a lookup field in subform in module Y

      Hi there In the 1st screenshot attached, you can see a subform in myLeads module. You can see that there is a number already filled there - that is the 'Property ID' and it is a single line field. It is the 'Property ID' of an entry I have in another

    • how to create a new line in string in Client Script?

      I want to show an alert using client script, I need to add a new line in String, I assume I can use \n\n inside a string, but unfortunately it doesnt work ZDK.Client.showAlert("First Line \n\nI expect this is in second line");

    • Surely it's time Inline editing from views

      I think the first request I found for in-line editing from grids was approximately 12 years ago - that post was locked because it was suggested Zoho sheetview solved the problem. However, it's now 2024, and in-line editing from grids is just a basic expectation.

    • Multi branding issue with sender addresses

      Hello, I'm currently working on a project involving two (seperate) brands. Named 'Windeck' and 'Prolance'. They've chosen CRM Plus and I'm currently working on CRM, SalesIQ, Social and Marketing Automation. So far, I'm able to make enough separations

    • Is there API Doc for Zoho Survey?

      Hi everyone, Is there API doc for Zoho Survey? Currently evaluating a solution - use case to automate survey administration especially for internal use. But after a brief search, I couldn't find API doc for this. So I thought I should ask here. Than

    • Email Campaigns overview page is missing SENT DATE and # people sent to!

      I would like to see the date the email campaign was sent, so I can understand and track when each email campaign was sent. Right now, unless you go to a contact who received a campaign, you cannot see when the campaign was sent (!!!!!!). So, if my boss

    • SEO recommendation of H1 tag for website tittle

      The exact words are “ It is good practice to place the page title inside the H1tag.” Now I already have one H1 tag on my website but it is not website tittle. In the SEO recommendation that is clear too that I have h1 tag on my page. Now I don’t know

    • How to choose other payment methodes than creditcards

      We have connected stripe as a payment provider in zoho books, booking, commerce and checkout. In stripe we selected al major payment methodes for Belgium (mainly bancontact). However, at checkout customers seems to have only the possibility to pay with

    • How to call a Creator function which is in a different Creator application?

      How to call a Creator function which is in a different Creator application?

    • Can the code in my "Successful form submission" WF be invoked from a function?

      Can "Successful form submission" be invoked from a function? Data gets into a form manually and programatically. My code in "successful form submission" is good and I want to reuse it/call it, from another function which does Insert Into How to achieve

    • Kaizen #169 - Serialization and Schema Management in Queries

      Hello everyone! Welcome back to another post in the Kaizen series! In Kaizen #166, we discussed handling Variables in Queries and associating the query in Kiosk. This week, we will discuss Serialization and Schema management in Queries. Business Scenario

    • Introducing Keyboard Shortcuts for Zoho CRM

      Dear Customers, We're happy to introduce keyboard shortcuts for Zoho CRM features! Until now, you might have been navigating to modules manually using the mouse, and at times, it could be tedious, especially when you had to search for specific modules

    • Feature Request: Notebooks within notebooks (Tree-like structure)

      Dear Zoho! I already migrated all my stuff from Google Keep, Im really fond of Zoho Notebook so far. One thing that could make the service much more powerful is multi-level notebooks (or tree like structure). For example, entering into Notebook named

    • Can't get authorization for Sandbox environment using the self client

      Hello, After creating a self client, and following the client-credentials method (as it's not optimat to manually generate a code for every 10 minutes), after inputting the sandbox org id for SOID parameter, im getting the error: "error": "no_org". For

    • Create landing pages from Zoho Marketing Plus

      Hey everyone, Over the last few months, we've introduced various features and enhancements to bolster the marketing capabilities of Zoho Marketing Plus and make it simpler for everyone. To that end, we're excited to announce that Zoho LandingPage is now

    • Custom service report or Zoho forms integration

      Hello, So far the experience with Zoho FSM and the integration with Books has been good, however there are limitations with service reports. As with my business, many organisations send technicians to different types of jobs that call for a different

    • Email tracking subdomain

      The Email Tracking configuration screen of the ZeptoMail asks for a subdomain. I have gone through the documentation but could not find more information about how that subdomain is used by ZeptoMail to track the emails. Can someone throw some light about

    • Chart View group X-axis values above a value

      I have a data set with X values ranging from 0 up to 300-400, the Y values are an AVG of the values for the given X. I am interested in the values at the low end of the scale, say 0-10 and want the X values 10 and greater to be grouped into a single category

    • How do I get the Text Account name instead of the Reference number?

      Good Morning everyone! I am very new to zoho analytics. I'm trying to create some pivot tables and when I add the Account or Division name it comes up as a reference number. From my research, I need to use a lookup. I'm having a really hard time understanding

    • Custom API - Need to create a string return value, not only MAP

      @Support: When creating a Custom API it only allows a return from a function of MAP type. The service I'm using requires a string return, how can this be achieved?

    • missing video-urgent

      hi..I have a problem regarding zoho meeting. I already record almost 2hrs for my interview session. After end my session, i'm stop the recording. Its happened when i didn't received any meeting recording at my email.But i received recording 7minutes after

    • Duplicate New Ticket Emails

      I am getting two Ticket emails when a ticket is created. The first one goes to me as the Admin helpdesk@ And because I am an Agent as well it also send to helpdesk@, support1@ and support2@ Is there any way to stop this?

    • Ticket Response Email Template

      Is there a way to have an email template automatically selected when I click on Reply for a ticket?

    • Unpublishing Job Post for LinkedIn

      Hi, We have experienced issue with the above. We did unpublish a job in Recruit but it still appear under Job section in our LinkedIn page. It did not remove from LinkedIn as it should be.  Please check and advise. Regards, Snyder 

    • Next Page