6 Replies Latest reply on Jan 24, 2019 4:31 AM by Ciara Brennan

# Calculating Business Days (No Weekends/No Holidays)

Hey Everyone,

I'm sure this has been posted before but just thought i'd share a calculation I use to calculate business days as it's useful for coming up with averages/run rates based on month.

1) Create first Calc and name it: 'First Day (current month)'

Enter the Following Calc:

 DATE(DATETRUNC('month',TODAY()))

2) Create second Calc and name it: 'Last Day (current month)'

Enter the following Calc:

3) Create a third Calc and name it: 'Business Days'

Enter the following Calc: NOTE - this automatically subtracts 1 day from each of the following months (January, May, July, September, November, December) which represent (New Years Day, Memorial Day, Independence Day, Labor Day, Thanksgiving and Christmas Day). You can adjust the months to your liking....

 IF ((MONTH(DATE(TODAY()))=1) OR (MONTH(DATE(TODAY()))=5)OR (MONTH(DATE(TODAY()))=7) OR (MONTH(DATE(TODAY()))=9)OR (MONTH(DATE(TODAY()))=11) OR (MONTH(DATE(TODAY()))=12)) THEN((DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -DATEPART('weekday',[Last Day (current month)])) / 7*5+ MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))-1) ELSE (DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -DATEPART('weekday',[Last Day (current month)])) / 7*5+ MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))   END

Now you can reference business days in any other calculations you have

EDIT: Thanks Dan Cory for the suggestion.

• ###### 1. Re: Calculating Business Days (No Weekends/No Holidays)

Good Job

• ###### 2. Re: Calculating Business Days (No Weekends/No Holidays)

Thanks Deepak!

• ###### 3. Re: Calculating Business Days (No Weekends/No Holidays)

Thanks for sharing this, Albert!

• ###### 4. Re: Calculating Business Days (No Weekends/No Holidays)

You're very welcome Ciara!

• ###### 5. Re: Calculating Business Days (No Weekends/No Holidays)

You might want to use a more efficient way to check the month of the current date:

(CONTAINS(DATENAME('month',TODAY()),'January'))

can just be written as:

MONTH(TODAY())=1

This will be much faster and work no matter your language!

2 of 2 people found this helpful
• ###### 6. Re: Calculating Business Days (No Weekends/No Holidays)

Cheers for this, Dan