As I mentioned in my previous blog, we already have a date range, and when we need to assign metrics to each day, we need to expand the dates into a list of dates in days.
Then, if we need to consider working days and working hours, how to achieve it? I explain one method clearly in this blog. Hope it helps.
The first step is to extend the time range which corresponds to each case to weekday time.
Then exclude the cases with holidays and weekends in the case date range.
1. Sample data
2. Construct List
In this case, the Start Time column contains not only the date, but also the time. As we know that the date with time is a decimal number when converted to a number, and the decimal number can't build the list. According to the sample data, [Start Time] is an integer except for the first row of each [ID] which is a decimal. The [End Time] is not an integer but can be obtained by converting the integer to time and subtracting 1 second from it, except for the last line which is a decimal.
So we construct a list of integers by rounding up and down, first removing the first and last, and then connecting the first and last individually with & to get the final list.