Jump to content
  • 0
JeremyMenefee

Adding Business Days to a Date Field

Question

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!

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0
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

Share this post


Link to post
Share on other sites
  • 0

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

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...