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

    • Send email is not authenticated

      Hi, I’m getting an error in Gmail, when receiving an email from my account in zoho, my email is already authenticated in my domain, and I don't know why I keep receiving this message... also testing in outlook, the message goes directly to "junk".

    • Using Queries with dynamic parameters in Kiosk Studio

      Hi, I'm pretty new when it comes to developing within Zoho (I'm really a .NET developer), as it was just added to my responsibilities. For a new feature in the CRM, I'm trying to develop a Kiosk function to show a list of records (retrieved by the new

    • Zoho sheets to crm and mapping

      i want to know more about mapping the contacts that i upload from the spreadsheet also couldnt able to understand flow

    • Condition-based data sharing rules are now available in CRM

      Dear All,   We are excited to introduce a much awaited feature: condition-based data sharing rules. We'd love to explain more about this enhancement.   Specify record conditions in data sharing rules Data sharing rules enable you to share all the CRM

    • Automatic Portal invite

      We have numerous customers we move through a blueprint in deals, when they get to a certain point we need to give them portal access, how can this be done through deluge or a workflow?

    • Is it possible to trigger the review process when a record is edited?

      Hello, I need to trigger a review process whenever a field is updated to a specific value. This field is empty when the record is created and is only filled later. I know the approval process exists, but that's not what I'm looking for in this case. What

    • 【Zoho CRM】ウィザード機能のアップデート

      ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 今回は「Zoho CRM アップデート情報」の中からウィザード機能のアップデート をご紹介します。 今回のアップデートにより、ウィザードの「条件処理ルール」機能が改良され、以前の入力画面の項目を基に条件を設定できるようになりました。 これまでは条件設定が1つの画面内に限られていたため、画面間で情報を関連付ける際に、余分な項目を作成する必要がありました。 今回のアップデートにより、前の画面の情報を利用して次の画面に条件を適用できるため、設定がより効率的になります。

    • Partner with HDFC And Sbi Bank.

      Hdfc and sbi both are very popular bank if zoho books become partner with this banks then many of the zoho books users will benefit premium features of partnered banks.

    • Send a formatted CV to multiple contacts at multiple clients - and have it associate with both candidate client

      I would like to send a formatted CV of a candidate to multiple contacts at multiple clients and have it kept on the record of all three; client, contact and candidate. I understand how to send an email to multiple contacts at multiple clients - and indeed I am able to attach the CV of a candidate through 'browse'. Obviously this does not associate it with the candidate though, only the contact it is sent to. Is there any way to do this? Thanks in advance.

    • Set organization level access to Knowledge Base and manage duplicate article permalinks (with date appended to them)

      Hello everyone, We have introduced two important updates in the Knowledge Base module: Permissions - A new permission called "Admin access" has been introduced under module permission. Permalink alert - Users will be alerted when an article with the same

    • Language Field on Contact Person-level

      Dear at Zoho Books, would it be possible for you to have a Field for 'Language' for the Contact Persons under a Company. In CRM and Bigin we could create a Custom Field (Dropdown) for this effect but without any present in Zoho Books we could never sync

    • Zoho Books email notifications

      Hi, We are currently implementing Zoho books for our organisation. I have 2 questions. 1. Are we able to embed HTML into our emails to make them look as part of our business ? Branding is very big issue for us and i would like to make sure that all branding efforts are carried across into the notification emails.. 2. Is there a way to preview the notifications that are sent out to our customers so we can make sure the look and feel is as we require ? thanks

    • send file to ftp or another external service

      i'v created a zoho creator application for take a picture and rename it by phone. Now i need to send Each renamed pictures to my ftp or to specific folder on google drive...then, delete it from creator. (every picture recived it will processed by another program and stored on my Erp) HOW CAN I DO ??

    • Plug Samples #12: Verify Customer Mobile Numbers with OTP to Avoid Spam Entries

      Hi Everyone! Verifying customer information is critical in business to filter out junk data and spammers. Whether it’s lead generation, appointment booking, or any other operation, ensuring quality data is essential for driving meaningful outcomes. A

    • All notes disappeared

      I've been using the notebook app for over five years on my phone without being logged into an account. A few days ago I opened the app and all my notes had disappeared. Since then I tried restarting my phone, updating the app and logging into my account,

    • Cost Center accounting

      Hello all. We are looking at switching from MYOB to Zoho books. Love all the features in books - esp. CRM integration, but the big gap for us is the lack of cost-center accounting feature. Are there firm plans to develop this feature in Zoho Books? If

    • Impuesto automatico en cotizaciones

      Buen dia Mi pregunta es como se puede poner alguna operacion para que las cotizaciones me salgan automaticamente con impuestos ya que uno al cargarla al final tienen que añadir el porcentaje de impuesto Saludos

    • Formatting of Gantt Chart for PDF export

      Hi all, There really needs to be some improvements to the presentation of exported PDF Gantt Charts. An exported format is the way that most people on a project (other than the PM directly) will consume this data. The current PDF export options are lacking a lot of control. - I'd like to firstly have control over the export size of the PDFs (A4, A3 etc), Currently it seems arbitrary - very large aspect ratio print sizes. - Next i'd like to be able to specify the print date range, and scale (show

    • How to Iterate a Function in Zoho Desk Workflow with Delay Between Calls?

      Hi everyone, I’m working on a function in Zoho Desk that searches for a specific ticket record. If the ticket is not found, I need to retry the search multiple times with a delay between each attempt until the ticket is located or a maximum number of

    • タスクを問い合わせテンプレートに追加

      タスクを問い合わせテンプレートに自動で追加されるようにしたいです。どうしたらいいですか

    • Placeholder for Agent Signature in Email Templates

      Dear Zoho Team, I hope this message finds you well. We currently face a limitation when designing email templates in Zoho Desk. While we can create email templates and include a footer at the end, the agent signature is always appended by default at the

    • Custom Profile Agents Unable to Update Mandatory Phone Number in tickets under Zoho Desk

      Hi, While working in the Ticket module in Zoho Desk, agents with a custom profile are unable to update the Phone Number field (which is mandatory) under Account Information. The agents need to enter values such as "NA" or "Nil" in the field to close the

    • Updating existing values in a list

      Is it possible to update an existing value in a list? For example if I wanted to append text to an existing string value that I had previously added to a list is that possible? I'm able to extract the existing value (get() function) and append the additional text, but can't find a way to add the new value back to the list in the same index location. One work around I found was to delete the existing list entry and then add the new value, however this changes the index location to the last entry in

    • Host not reachable

      Good morning. I have four mail accounts and they have been working well for years. Now, suddenly, I am unable to send any e-mails from any of them. I allways have this pop-up: Thank you. Regards. Luis Fernández

    • Zoho Marketing Campaign

      I want a details report of marketing API . which API i can use to get a full flexed detail of email campaign , sms , social media ,and all other campaigns ?

    • Zoho Marketing Automation APIs

      When I want to create New lead in marketing automation , I want to add First Name and Last Name as well along with Email but there is no option like this in API . Can you please give me the API which will fulfill my condition ?

    • Adding Folders in Android App

      Is it possible to create a new email folder within the Zoho Mail Android app?  Or can this only be done from the desktop version of Zoho Mail? Cheers!

    • Collaborative editing of spreadsheets by multiple users simultaneously - Is it really possible?

      I work in a SharePoint 2010/SharePoint Server 2010 environment and we use MS Office 2007(Excel 2007) . I have been trying to create a MS Office document which will allow multiple users to work on a spreadsheet simultaneously. For that I have: 1) Created a excel workbook and published as a workspace as suggested by Andrea Kalli in her link: http://www.wonderhowto.com/how-to-collaborate-files-between-sharepoint-and-office-2007-182172/ 2) Set up and saved the Excel Workbook as "Shared" and made it available

    • Ability for customer to give feedback after receipt of an order

      Is there any way we can receive feedback from customers regarding their order after delivery (other than just an email, obviuosly)? This is not the same as product reviews, as it may concern other points, but would ideally have an inbuilt reference to

    • Zoho Books Sandbox environment

      Hello. Is there a free sandbox environment for the developers using Zoho Books API? I am working on the Zoho Books add-on and currently not ready to buy a premium service - maybe later when my add-on will start to bring money. Right now I just need a

    • Zoho Books | Product Updates | November 2024

      Hello users, We are back with exciting new features and enhancements in Zoho Books. From multi-criteria workflows to writing off customer opening balances, discover the new updates designed to make your bookkeeping swift. Attach XRechnung to Invoices

    • Spell check sucks

      Come on guys, it's 2024 and your spell check is completely retarded. You gotta fix it.

    • How to send certain invoices for Approval based on condition?

      To streamline operations, I need the invoices to go for approval when certain conditions are met. For example, if my staff gives a discount of x amount, it must go for approval. If there is no discount, then the invoice will be approved automatically.

    • Signature image logo not displaying in recipient mail clients

      Whenever I send an email to another person the image that forms part of my logo is not displayed just a broken link to: https://mail.zoho.com/mail/ImageSignature?fileName=Email-log.jpg&accountId=47238000000007001&storeName=NN1:-3205531763309921492 I can see the logo because I've got a zoho account and I'm logged on but others without a zoho account can't see it. How do I fix this? Thanks -Martin

    • Sales Order, what are the statuses under Confirmed and Closed

      Hi, I have to build a workflow in Deluge which should be triggered when Sales Order status is Confirmed or Closed. But these 2 states don't exist when you fetch a sales order. Which of the statuses are considered as Confirmed or Closed ? Here is a list

    • Notifications for calendar meetings

      So we have been happy users for a few months now. This is an important problem we face, since I have forgotten meetings already cause of lack of notifications. The problem: I have notifications (email, popup and notifications) set for my calendar by default.

    • Reusuable Sections & Universal Brand Settings

      Zoho Email Campaign setups take longer than other software I've used because it doesn't have reusable sections or universal brand settings (fonts and colors). These enhancements should be added to help us create our campaigns more easily.

    • Blockchain technology

      Blockchain technology is being used to revolutionize accounting and financial reporting. With blockchain, financial transactions are recorded on a decentralized ledger, making tracking transactions and verifying their accuracy easier. This technology

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

      Fui fazer meu cadastro na zoho e 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? Atenciosamente, Anderson Souza.

    • not able to hit inventory api

      when i hit the api with my account keys and the access is provide by the client

    • Next Page