How to calculate working hours in Excel - Part 1

A key requirement in business analysis is to calculate ageing. 

We need to calculate ageing of goods in stock for a warehouse, customer requests in a customer care center or time outstanding for a loan

In addition the ageing has to be often calculated based on working hours and this aspect is as crucial as it is complex 

In this post we will start to explain how to run a report to split a list  of customer requests’ in three different ageing buckets:

• From 0 to 4 hours

• From 4 to 8 hours

• Above 8 hours

We will be calculating the ageing in working hours that go from 08:00 to 13:00 and from 14:00 to 17:00

The first step is to create a calendar with working hours. In next posts we will be building on the content of this post and go deeper in the calculations

In order to create the calendar follow these steps:

In Column A row 1 we insert the number 1. In the cells below we copy the following formula 

Column B will contain the dates In row 1 key enter the date you select

The next rows will be be filled with the formula reported below. The formula in column A returns 0 when a new day is starting and therefore the date in column B is increased by 1. Otherwise the same date is reported

Column C will report the hours of the day. The hours will be obtained by dividing the value in column A by 24. 

The result has to be displayed in the HH:MM format 

The first objective is to determine which date and time are working hours 

In order to achieve this result we will be inserting formulas in columns from I to F

Column I will contain the formula to determine if the date is Saturday or Sunday 

Column H will calculate if the time is between 12:00 and 13:00

Column G will identify if the time is between 17:00 and midnight 00:00

Column F will show if the time is between 00:00 and 08:00

The formula in each column will return TRUE if the conditions are met and FALSE if they are not 

Column E sums columns F, G,H and I. Yes they can be summed as TRUE equals 1 and FALSE equals 0 

Column E allows to understand if the date and time reported on the same row are within the set working hours or not

To track working hours, we start by assigning the number 1 to column D.

For subsequent rows that represent working hours, we increment the value in column D by one, thus sequentially numbering each hour.

When the subsequent row does not represent a working hour the previous number is repeated 

All the formulas are copied down all the rows that represent the period for which we want to calculate the working hours

The difference of the values in column D placed on two different rows represents the number of working hours elapsed between the dates and times placed on the two rows

The next post explains how to leverage the difference between the numbers corresponding to arrival time and reporting time in order to calculate the ageing in working hours