Calculate Production Per Hour in Excel

In this section, we will cover how to calculate the production per hour in Excel.

The production per hour is a measure that tells us how much work is done in an hour of time. It is calculated by dividing the total number of units produced by the total hours worked.

Calculate production per hour

We use the following dataset showing the production of employees in our illustration:

Table

Description automatically generated

We use the following steps:

  1. Select cell D2 and type in the following formula:
=C2/(B2*24)
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.
Table

Description automatically generated
  1. Select range D2:D7.
  2. Click Home >> Number >> Number Format Arrow >> Number.

The values in the range D2:D7 are now displayed in Number format:

Table

Description automatically generated

Explanation of the formula

=C2/(B2*24)
  • (B2*24) The time in cell B2 is multiplied by 24 to convert it to a decimal value. This is because in Excel time is represented as a fraction of a 24-hour day. Time is internally stored as a decimal number where 1 represents 24 hours. An hour is a one-twenty-fourth of a day, and its serial number is 0.0416667.
  • The serial number of the time 3:50 in cell B2 is 0.159722. Tip: First divide the 50 minutes by 60 to convert them to a decimal. Then divide the 3 hours and the decimal by 24.
  • =C2/(B2*24) becomes =60/(0.159722*24). The result is 16.65 shown in cell D2.

Conclusion

Excel stores time as a decimal number where 1 represents 24 hours. Every hour is stored as a fraction of 1. This knowledge is essential in calculating production per hour in Excel.