7 Replies Latest reply on Jan 5, 2017 7:34 PM by Walt Reed

# How to calculate business days between two dates in different years

Hi,

I'm trying to calculate business days between 2 dates. My formula works when the 2 dates fall into the same year, but how to make it work when the 2 dates fall into different years? Pls. help. Thanks!

DATEDIFF('weekday',[End Date],[Start Date]) - 2 * (DATEPART('week',[Start Date]) - DATEPART('week',[End Date])) + (IF DATENAME('weekday',[End Date]) = 'Sunday' THEN 1 ELSE 0 END) - SUM([No of holidays])

Emily

• ###### 1. Re: How to calculate business days between two dates in different years

Hey Emily,

Looking at your formula, I think you have [Start Date] and [End Date] switched in the first two parts of the calculated field (DATEDIFF and DATEPART).

Here's my equation that appears to work:

DATEDIFF('weekday',[Start Date],[End Date]) -2 *

(DATEPART('week',[End Date])-DATEPART('week',[Start Date])) +

(IF DATENAME('weekday',[End Date])='Saturday' OR DATENAME('weekday',[Start Date])='Sunday'

THEN 0 ELSE 1

END)

Let me know if this solves the issue.

Walt

• ###### 2. Re: How to calculate business days between two dates in different years

Hi Walt,

Thanks for the reply! My typo, actually my formula is as follows, but it doesn't work when the start date and end date fall into different years:(

DATEDIFF('weekday',[End Date],[Start Date]) - 2 * (DATEPART('week',[End Date]) - DATEPART('week',[Start Date])) + (IF DATENAME('weekday',[End Date]) = 'Sunday' THEN 1 ELSE 0 END) - SUM([No of holidays])

Emily

• ###### 3. Re: How to calculate business days between two dates in different years

Hey Emily,

That's strange...because I used my formula on the Superstore data and it

calculated business days, even across different years. Do you have a

workbook you could share?

Walt

• ###### 4. Re: How to calculate business days between two dates in different years

Hi Walt ,

Can you  please share your workbook so that we will come to know how to implement the business days .

Regards

Kumar

• ###### 5. Re: How to calculate business days between two dates in different years

Hi Emily,

At a glance I'm suspecting you are having an issue with the SUM([No of Holidays]) part. If you removed this, are you able to get the correct number of week days?

• ###### 6. Re: How to calculate business days between two dates in different years

Hi Areti,

Thanks for the reply but it's not because of the sum([No of Holidays]).

Emily

• ###### 7. Re: How to calculate business days between two dates in different years

Hi Walt,

I found another way to resolve the issue. Below formula works:

DATEDIFF('week',MIN([Start Date]),[End Date])*5 - MIN(DATEPART('weekday',[Start Date],6)

+ MIN(DATEPART('weekday',[End Date],6)+(IF DATENAME('weekday',[Start Date])='Saturday' OR DATENAME('weekday',[Start Date])='Sunday' THEN 0 ELSE 1 END)

Emily