Adding Business Days to a Date Field

[JeremyMenefee]

I need to create a formula that will calculate a date based off of how many business days a number field is. Basically I need to mimic the WorkDay formula in excel. Does anyone know of a way to accomplish this in Caspio?

This is a link explaining the workday formula: https://www.extendoffice.com/documents/excel/3445-excel-add-days-to-date-excluding-weekends-and-holidays.html

 

Thanks!

[Mathilda]

On 12/9/2017 at 2:13 AM, JeremyMenefee said:

I need to create a formula that will calculate a date based off of how many business days a number field is. Basically I need to mimic the WorkDay formula in excel. Does anyone know of a way to accomplish this in Caspio?

This is a link explaining the workday formula: https://www.extendoffice.com/documents/excel/3445-excel-add-days-to-date-excluding-weekends-and-holidays.html

 

Thanks!

Hi, this solution requires creating a sql function, which is not possible in formula or calculated field.

I usually post my requests which fall out of standard behavior here, you may post your idea there as well

[vikovs]

Hi there,

In case someone needs, you may try the following solution to count ONLY business days and exclude weekends and Holidays (you may need to have a separate table with State holidays information)

To calculate the difference in dates (only considering business days), enter the following into your calculated field:

SELECT
   (DATEDIFF(dd, [@field: StartDate], [@field:EndDate]) + 1)
  -(DATEDIFF(wk, [@field:StartDate], [@field:EndDate]) * 2)
  -(CASE WHEN DATENAME(dw, [@field:StartDate]) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, [@field:EndDate]) = 'Saturday' THEN 1 ELSE 0 END) 

  -(SELECT count(id) FROM Your_State_Holidays
WHERE date BETWEEN [@field:StartDate] AND [@field:EndDate]
AND DATENAME(dw, date) NOT IN ('Sunday', 'Saturday'))

where you replace StartDate and  EndDate with your corresponding fields and Your_State_Holidays wih the name of your table

 

 

[RuisiHansamu]

Hello, you may want to refer to these similar forum topics. Here are helpful tips from them as well: 

 

 and 

 

[PotatoMato]

Hello, just to update this post. You may use the formula below:

DateAdd(day, 15 +(15 / 5) * 2, [@field:Date_field])

15 is the business days that will be added to the date field. You may also use an actual field.

-Potato