3 Ways to Calculate Time

By Jonathan Lim

About time

Let’s say it is 8:00:00 am now, and I want Excel to calculate the time 1 hour, 1 minute and 1 second earlier or later.
There are three methods by which this can be done:

1.  The Long Way

Using a constant of the hour, minute and second

  • An hour is approximately 1 / 24 of a mean solar day;
  • A minute would be = 1 / (24 x 60)
  • A second would be = 1 / (24 x 3600)

The following range names have been created for this exercise:

HrsNum        Cell C45
HrUnit           Cell D45
MinsNum      Cell C46
MinuteUnit   Cell D46
SecondUnit   Cell D47
SecsNum       Cell C47

Cells C45 to C47 are hardcoded inputs that the user can tweak.  In this example, the default inputs are 1 hour, 1 minute and 1 second.
Based on this, the formula would look like this:

= B51 – (( HrsNum * HrUnit) + ( MinsNum * MinuteUnit) + ( SecsNum * SecondUnit))

2.  Using the SUMPRODUCT function

The SUMPRODUCT function multiplies the ranges in columns C and D and returns the sum of the results that would be subtracted from our start time of 8:00:00 am. The formula would look like this:

= B51 – SUMPRODUCT( $C$45:$C$47, $D$45:$D$47)

3.  Using the TIME function

In Excel, the TIME function returns the decimal number of a particular time, which can be added to/subtracted from to the timing stated above. The syntax for this function is:

TIME(hour, minute, second), where
hour is a number from 0 to 23;
minute is a number from 0 to 59; and
second is a number from 0 to 59

Using the TIME function, the formula would look like this:

= B51-TIME( HrsNum, MinsNum, SecsNum)