Adding business days and excluding weekends from formula field in Zoho CRM

[Zoho CRM] Adding business days and excluding weekends from formula field

In Zoho CRM, or in any other Zoho apps that have a formula field, it is possible to configure it to return a date value and exclude weekends. For example, if the Opening Date of a Deal is today, I want to make the deadline for addressing it 2 days after it is created. However, I don't want it to take into account weekends. So if the Opening Date is Thursday, the formula should return the next Monday as the deadline date. This is doable in the formula-type field in Zoho CRM specifically. Let ${Deals.Days Added} be the number of days that would be added to the Opening Date. Create a formula field and have the return type as "Date" and write the following formula. Change the field name accordingly to your respective field names:

If(Weekday(Adddate(${Deals.Opening Date},${Deals.Days Added},'DAY'))==1,Adddate(${Deals.Opening Date},${Deals.Days Added}+1,'DAY'),If(Weekday(Adddate(${Deals.Opening Date},${Deals.Days Added},'DAY'))==7,Adddate(${Deals.Opening Date},${Deals.Days Added}+2,'DAY'),Adddate(${Deals.Opening Date},${Deals.Days Added},'DAY')))

Let's dissect this. "If" is a conditional formula to ask the system to return a value when it satisfies a certain criterion/criteria.
"Adddate" is a function to add a unit of time (day, month, year) to a date-time value. You write it like this: Adddate(date_value_here,number_you_want_to_add, 'UNIT')
"Weekday" function will return you a decimal number from 1 to 7 representing Sunday to Saturday. 1 is Sunday, and 7 is Saturday. Hence, the formula is essentially asking,
  1. if Opening Date + days added = Sunday, add 1 more day to Opening Date + days added
  2. if Opening Date + days added = Saturday, add 2 more days to Opening Date + days added
  3. Else, return Opening Date + days added
Got it? Great! Let us know if you need any help on this. Good luck!

    • Related Articles

    • [Zoho CRM] Get Birthday Months Formula

      Create a custom formula field and make sure the return type is String. Then, write this formula: If(Month(${Contacts.Date of Birth})==1,'01',If(Month(${Contacts.Date of Birth})==2,'02',If(Month(${Contacts.Date of ...

    • [Zoho CRM] A Comprehensive Guide for Zoho CRM Wizards

      Unleashing the Power of Zoho CRM Wizards: A Comprehensive Guide What is CRM Wizards? Zoho CRM Wizards are a powerful tool designed to simplify and enhance the data entry process in Zoho CRM. They enable users to break down lengthy forms into a series ...

    • [Zoho CRM] Copying Field Value to Another Field

      In Zoho CRM, it is possible for you to copy a field's value to another field; given if the data type is compatible. What do we mean by this? For example, if you're looking to copy from a text field to a date field, this is not possible as the data ...

    • [Zoho CRM] Creating A Lead

      Leads in Zoho CRM can be created in several ways; Direct creation Import Social media connection (Facebook and Twitter) Zoho Mail integration Zoho Cliq (website ) integration To create directly, click on the '+' icon at the top right of the screen. ...

    • [Zoho Recruit] Adding/Editing Field Dependency

      To add/edit field dependency in Zoho Recruit, go to Setup > Modules > Hover on the module the parent/child fields are in, choose "Map Dependency Fields" There, you can choose to map what child field values to which parent field value. Make sure you ...