Hi there!
Chandler from the Sigma Support team here!
In this post, I’ll show you how to:
- Convert timestamps for events to business hours
- Calculate the difference in business hours between two events
Note that this method:
-
Is easily customizable to your business hours
-
Only works for business hours that begin and end on the hour (eg: 8:00am, 6:00pm, etc.)
-
Only works for workweeks of Monday to Friday
(although it can be adapted to other workweeks) -
Does not work for business hours that span across midnight
-
Does not account for holidays
Also note:
- I will use the terms “work hours” or “workday” instead of “business hours” for brevity - except in column names and formulas where I will use “Shift” for even more brevity.
Step 1:
For a given column of event timestamps, convert all timestamps that fall outside of work hours to the first work hour of the next work day.
Solution:
To start, you’ll need the following:
-
A column of event timestamps. We’ll name this column [Event1]
-
A column with the hour that your company’s workday begins, according to a 24 hour clock, as a number. We’ll name this column [ShiftStart].
(e.g. if your workday begins at 9:00AM, then [ShiftStart] = 9) -
A column with the hour that your company’s workday ends, according to a 24 hour clock, as a number. We’ll name this column [ShiftEnd].
(e.g. if your workday ends at 5:00PM, then [ShiftEnd] = 17)
or in short:
-
[Event1] = Your column of event timestamps
-
[ShiftStart] = The first hour of your work hours, as a number from 0 to 23
-
[ShiftEnd] = The last hour of your workshift, as a number from 0 to 23
You’ll then want to make the following columns with the given formulas:
-
[WeekdayEvent1] = Weekday([Day of Event1])
-
[HourEvent1] = Hour([Event1])
-
[ShiftLen] = [ShiftEnd] - [ShiftStart]
-
[PreShiftEvent1] = If([HourEvent1] < [ShiftStart], True, False)
-
[OnShiftEvent1] = If([HourEvent1] >= [ShiftStart] and [HourEvent1] < [ShiftEnd], True, False)
-
[PostShiftEvent1] = If([HourEvent1] >= [ShiftEnd], True, False)
-
[WorkweekEvent1] = If(([WeekdayEvent1] >= 2 and [WeekdayEvent1] < 6) or ([WeekdayEvent1] = 6 and (Not [PostShiftEvent1])), True, False)
-
[WorkweekTreatmentEvent1] = If([WorkweekEvent1] and [PreShiftEvent1], DateAdd(“hour”, [ShiftStart], [Day of Event1]), [WorkweekEvent1] and [PostShiftEvent1], DateAdd(“hour”, [ShiftStart], DateAdd(“day”, 1, [Day of Event1])), [WorkweekEvent1] and Not ([PreShiftEvent1] or [PostShiftEvent1]), [Event1], Null)
-
[WeekendTreatmentEvent1] = If((Not [WorkweekEvent1]) and [WeekdayEvent1] = 6, DateAdd(“hour”, [ShiftStart], DateAdd(“day”, 3, [Day of Event1])), (Not [WorkweekEvent1]) and [WeekdayEvent1] = 7, DateAdd(“hour”, [ShiftStart], DateAdd(“day”, 2, [Day of Event1])), (Not [WorkweekEvent1]) and [WeekdayEvent1] = 1, DateAdd(“hour”, [ShiftStart], DateAdd(“day”, 1, [Day of Event1])), Null)
-
[BizHoursEvent1] = Coalesce([WorkweekTreatmentEvent1], [WeekendTreatmentEvent1])
Explanation
This solution works by breaking each calendar week into functional areas that require similar treatments to be converted to work hours.
The two areas that differ the most in that respect are the Weekend (defined here as Friday evening after work hours, all of Saturday, and all of Sunday) and the Workweek (defined here as all of Monday through Friday, except Friday evening.)
For the Weekend:
- all timestamps must be converted to the first work hour of the first workday of the next week.
This is handled in the calculation [WeekendTreatmentEvent1].
For the Workweek:
- each timestamp that falls in the morning before work hours (or the “Preshift” period) must be converted to the next work hour of the same day
- each timestamp that falls in the evening after work hours (or the “Postshift” period) must be converted to the next work hour of the next day.
Note: The Friday Postshift cannot simply be converted to the work hour of the next day, which is why it falls under the Weekend treatment - each timestamp that falls within work hours (or the “Onshift” period) is simply unchanged
This is handled in the calculation [WorkweekTreatmentEvent1]
The results of these two calculations are joined by the calculation [BizHoursEvent1]
All other columns exist to identify areas of similar treatment within those two formulas. (Except for [ShiftLen] which is actually used in Step 2)
Notes on Use
If you want to convert multiple columns of event timestamps to work hours:
- Each event timestamp will need it’s own copy of every one of the columns listed above …
- … with the exceptions of [ShiftStart], [ShiftEnd], and [ShiftLen], which can be reused for multiple columns
- I highly recommend giving these columns names that refer back to the event that they are converting
Step 2
For two columns of event timestamps that have been converted to business hours, calculate the number of elapsed business hours between the two events
Solution
To start, you’ll need the following:
- Two columns of event timestamps that have been converted to work hours using the method in Step 1. We’ll name these columns [BizHoursEvent1] and [BizHoursEvent2], with [BizHoursEvent2] preferably being the event that is further forward in time.
- The column [ShiftLen] which was calculated in Step 1
or in short:
- [BizHoursEvent1] = A column of events converted to business hours using the method in Step 1. Preferably, this is the earlier of the two events
- [BizHoursEvent2] = Another column of events converted to business hours using the method in Step 1. Preferably, this is the later of the two events
- [ShiftLen] = [ShiftEnd] - [ShiftStart]
You’ll then want to make the following columns with the given formulas:
-
[RealHourDiff] = DateDiff(“minute”, [BizHoursEvent1], [BizHoursEvent2]) / 60
-
[DayDiff] = DateDiff(“day”, DateTrunc(“day”, [BizHoursEvent1]), DateTrunc(“day”, [BizHoursEvent2]))
-
[WeekDiff] = DateDiff(“week”, DateTrunc(“week_starting_monday”, [BizHoursEvent1]), DateTrunc(“week_starting_monday”, [BizHoursEvent2]))
-
[BizHourDiff] = [RealHourDiff] - ([DayDiff] * (24 - [ShiftLen])) - ([WeekDiff] * ([ShiftLen] * 2))
Explanation
This solution works by carrying out the following steps:
-
Calculate the real fractional number of hours between these two events.
-
Calculate the number of calendar days between these two events. Use it to find the number of non-work hours for every calendar day between the two events (erroneously including weekends as if they were workdays). Subtract this amount from the total from Step 1.
-
Calculate the number of weekends between the two events. The previous step assumed weekends to have work hours as if they were week days. To correct this, subtract two workdays worth of hours for each weekend between the two events from the total from Step 2.
Notes on Use
- This will not work unless both events have been converted to work hours
Please let me know if you have any questions, thoughts, or suggestions for improvement in the comments!
Best,
Chandler Phelps
Sigma Support
