3 Replies Latest reply on May 12, 2017 4:00 PM by Mark Bradbourne

# Calculating the number of Ship Days in Month

Hi

I am attempting to have Tableau count the number of ship days in a month. A ship day according to my company is all weekdays in a month excluding national holidays in the United States. An example of this is... In May there are 23 Weekdays but only 22 Ship days because of Memorial Day. Is there a function or calculation that I could use to accomplish this task.  I

Thank you

PS If this can be done without adding another Data Source that would be ideal.

• ###### 1. Re: Calculating the number of Ship Days in Month

Hey Wesley,

Here's a KB article that you might find useful. The second section is business days=weekdays excluding holidays. Unfortunately, to exclude holidays, I think you'll need an excel sheet with a list of what your company considers a holiday. Hope this helps!

Michael

• ###### 2. Re: Calculating the number of Ship Days in Month

The other option would be to code to check for the holidays :

`---- New Years Day (1/1/(YEAR)---- Martin L King's Birthday ( 3rd Monday in January )---- President’s Day ( 3rd Monday in February )---- Memorial Day ( Last Monday in May )---- Independence Day ( July 4 )---- Labor Day ( 1st Monday in September )---- Columbus Day ( 2nd Monday in October )---- Veteran’s Day ( November 11 )---- Thanksgiving Day ( 4th Thursday in November )---- Christmas Day ( December 25 )---- New Years Eve Day`

The trick will be when the holidays fall on Saturday or Sunday and they shift to the Friday or Monday... If I get bored over the weekend I might actually take a stab at figuring this out,

• ###### 3. Re: Calculating the number of Ship Days in Month

Here's the quick and dirty version, not shifting to Mondays/Fridays.

[Work Date Flag]

IF (

(DATENAME('weekday',[Order Date]) = 'Saturday' or DATENAME('weekday',[Order Date]) = 'Sunday') //Weekends

OR (DAY([Order Date]) = 1 and MONTH([Order Date]) = 1) // New Years Day

OR (Month([Order Date]) = 1 and DAY([Order Date])>=15 AND Day([Order Date])<=21 AND DATENAME('weekday',[Order Date])='Monday') // MLK's Birthday

OR (Month([Order Date]) = 2 and DAY([Order Date])>=15 AND Day([Order Date])<=21 AND DATENAME('weekday',[Order Date])='Monday') // President's Day

OR (Month([Order Date]) = 5 and DAY([Order Date])>=25 AND Day([Order Date])<=31 AND DATENAME('weekday',[Order Date])='Monday') // Memorial Day

OR (DAY([Order Date]) = 4 and MONTH([Order Date]) = 7) // July 4th

OR (Month([Order Date]) = 9 and DAY([Order Date])>=1 AND Day([Order Date])<=7 AND DATENAME('weekday',[Order Date])='Monday') // Labor Day

OR (Month([Order Date]) = 10 and DAY([Order Date])>=9 AND Day([Order Date])<=15 AND DATENAME('weekday',[Order Date])='Tuesday') // Columbus Day

OR (DAY([Order Date]) = 11 and MONTH([Order Date]) = 11) // Veterans Day

OR (Month([Order Date]) = 11 and DAY([Order Date])>=22 AND Day([Order Date])<=28 AND DATENAME('weekday',[Order Date])='Thursday') // Thanksgiving Day

OR (DAY([Order Date]) = 24 and MONTH([Order Date]) = 12) // Christmas Eve

OR (DAY([Order Date]) = 25 and MONTH([Order Date]) = 12) // Christmas Day

OR (DAY([Order Date]) = 31 and MONTH([Order Date]) = 12) //New Years Eve Day

)

Then 'No'

Else 'Yes'

End

Then create a calc that that counts distinct when the date calc is "yes"

[Work Days]

IF [Work Day Flag] = "Yes" then COUNTD([Order Date])

End