# How to calculate work days?

Customer question - How can I calculate work days in QuickSight? (ie - exclude weekends and other specific holidays)

You can do work day calculations using a combination of existing calcs.
I have posted a sample at https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Work-Days

Calculation used is

``````min(
//Get the days between start and end dates.
//If this is 0 or negative, the end date is same or prior to start date. So, set the work days to 0
ifelse(dateDiff({Start Date},{End Date},'DD') <= 0, 0,
//If end date is later than start date, let's calculate further.
//Get the sunday prior to start and end dates using trunDate function,
//get the number of weeks between those Sundays using outer dateDiff and multiply by 5 to get work days therein.
dateDiff(truncDate('WK',{Start Date}),truncDate('WK',{End Date}),'WK')*5
//Since we are calculated above days from Sunday prior to start date,
//we got to subtract number of working days between that Sunday and actual start date.
//ie if actual start date is on Sunday or Monday, subtract 0.
//If it is Tue, subtract 1, so an and so forth.
//When we extract weekdays, Sun is 1 and Sat is 7. So, we can get the value to subtract using below logic
- ifelse(extract('WD',{Start Date}) = 1, 0, extract('WD',{Start Date})-2)
//Along same lines, we got to add the weekdays from Sunday prior to end date
+ ifelse(extract('WD',{End Date}) = 1, 0, extract('WD',{End Date})-2)
//Next, account for any other holidays other than weekends by making an explicit entry below for each date.
//Uncomment below block if you want to see that changes if Dec 30th and 31st are considered holidays.
//-
//(
//    ifelse({Start Date}<='2021-12-30' and '2021-12-30' <{End Date}, 1, 0) +
//    ifelse({Start Date}<='2021-12-31' and '2021-12-31' <{End Date}, 1, 0)
//)
)
)
``````
2 Likes