Working days or hours in a month

Calculating the number of working days and/or working hours in a month can be accomplished relatively easily with the use of the Elapsed function.

The basic function of Elapsed is to calculate the number of seconds between two dates/times.  However, the function also has some optional argument that allow us to exclude dates in the range that we are evaluating by way of an Exclusion table.

The first thing we need to do is set up our exclusion table.  I'll call my table "Non Work Day Exclusions," and for this example, I will only be excluding weekends.  The table only needs a From and To field, but I'd also recommend a Description field.  My table looks like this:


I can also add specific days to my table (useful for excluding holidays).  Each excluded day would be an additional record in the table.  For example, to exclude Christmas Day, I would add another records where From="12/25/2021 00:00" and To="12/26/2021 00:00"

Next, I just need to create the formulas in my table.  The formula is:
=Elapsed("1/1/2021", "2/1/2021”, Non Work Day Exclusions, From, To)/(24*60*60)
The Elapsed function returns the number of seconds between 12AM on Jan 1st and 12AM on Feb 1st.  We then divide by 24*60*60 to translate seconds into days.  This gives us our result:


The really nice part about this formula is that you don't need to worry about the number of actual days in the month.  You're always going from the 1st of one month to the 1st of the next month.

This is a good start, but I want to make this a little more functional.  I'd rather not hard-code the date, so I'll create a few new columns to dynamically calculate the start and end date (with a little bit of magic to handle the year-end turnover scenario):


And finally, calculating the working hours for the month is just the simple formula of Working Days*8.


For additional information on Elapsed function, see: https://community.apptio.com/viewdocument/elapsed-function-1?CommunityKey=3ff74a27-8291-48d0-ba5a-403be94d07b8&tab=librarydocuments

#ApptioBI

#FormulasAndFunctions
#TBMStudio
#CostingStandard(CT-Foundation)