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