How to calculate working hours in Excel - Part 2

In the dynamic landscape of business operations, a fundamental metric is ageing

From accounts receivables, customer requests and inventory turnover, ageing is a key metric to measure the efficiency and productivity of company's processes

Calculating ageing in working hours allows to reach a more comprehensive understanding of the underlying processes. 

In some instances working hours is when the action takes place, therefore being able to analyse data in accordance to this time frame delivers more suitable and reliable metrics

In a previous post, we discussed how to construct a calendar based on working hours. 

A crucial aspect of this calendar is that each working hour is accompanied by an incremental number, while non-working hours retain the same number. We will refer to this number as the REFERENCE

This feature allows for clear differentiation between working and non-working times. 

Calculating the difference between the REFERENCE numbers corresponding to two selected hours becomes the pathway to determining ageing in working hours. 

Let's consider the scenario where we have a list of customer requests 

We aim to calculate the ageing in working hours between the time the request arrived (Arrival Time) and the time the report is generated (Reporting Time).

The criteria used to accurately calculate the difference in working hours will leverage the previously calculated difference.

The steps o be perforemd are the following:

1. The entire hour of the arrival time is considered.

2. The entire hour of the report time is considered.

3. The Difference in the REFERENCE numbers allocated to each hour is calculated, as mentioned earlier.

4. The number of minutes from the arrival hour is subtracted from the Difference. If the arrival hour is not a working hour, this step is ignored.

5. The number of minutes from the report hour is added to the Difference. If the report hour is not a working hour, this step is ignored.

6. The resulting number represents the total number of working hours and minutes elapsed between the two hours.

To illustrate how the process works, let's explore two examples.

Example 1

Example 2

In a case a day, out of Saturday or Sunday, needs to be flagged as non working day, such as national holidays, you can simply copy manually the reference number from the end of the previous day throughout the all day

In the next post we will show how to build VBA code to calculated the geaing in working hours for a list of customer requests