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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×