• 0

## 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?

Thanks!

## 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?

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 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 on other sites

• 0

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

and

##### Share on other sites

• 0

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

## 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.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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