Create a Formula for Entering a Series of Incremental Times in Excel

Whether tracking hours for employees or performing complex what-if analysis, you may find yourself needing a series of time values or a series that increments by so many hours, minutes and seconds. Fortunately, Excel’s TIME() function can generate time values and even an incremental time series.

In this article, you’ll learn how to implement the TIME() function. Then, you’ll learn how to increment time values by specifying the time interval as arguments and using a formula. You’ll also learn how to increment time values using input cells for a more permanent solution as well as using AutoFill for a faster solution.

I’m using Microsoft 365 on a Windows 11 system, but you can use earlier versions of Excel and Windows.

How to use Excel’s TIME() function

If you’re familiar with the DATE() function, you’ll find TIME() similar in purpose and implementation. DATE() can evaluate time values — they’re the decimal portion and represent a specific moment within the day.

SEE: Check out these Excel tips and tricks for beginners and pros.

Excel’s TIME() function returns only a decimal value that represents a specific time — there’s no date involved. The returned value will range from 0 to 0.99988426, representing times from 0:00:00, or 12:00:00 a.m., to 23:59:59, or 11:59:59 p.m.

The TIME() function uses the syntax TIME(hour, minute, second).

All three arguments are required, using a value to represent that time component between 0 and 32,767. The date arithmetic happens internally when combining the three time components into one decimal value.

Figure A shows a matrix of random hour, minute and second values in ascending order. The function in E2 — =TIME(B2,C2,D2) — is copied to the remaining cells in the matrix. This TIME() function combines the integer values in columns B, C and D into time values formatted to display all three components. Column F uses the General format, so you can see the corresponding decimal values.

The results require little explanation. For instance, 4, 17 and 23 in row 4 represent the fourth hour of the day, the seventeenth minute of that hour and the twenty-third second of that minute: 4:17:23 a.m. The values 17, 54 and 45 in row 12 represent the seventeenth hour of the day, the fifty-fourth minute of the hour and the forty-fifth second of the minute: 5:54:45 p.m.

TIME() is easy to understand and implement, but you won’t always want a specific time. What happens if you want a series of incremented time values?

SEE: Here’s how to use TODAY() to highlight fast-approaching dates in Excel.

How to generate incremental time values using a formula in Excel

If you need a series of incremental time values, you could enter them manually, but doing so would be tedious and prone to input errors. Instead, a simple formula using TIME() can do the trick. We can illustrate this with a simple formula that creates time values in increments of two hours:

1. To get started, enter the first value in the series. In this case, enter 10:00 a.m. in cell H2 (Figure B). To do so quickly, enter 10 AM, with a space between the two components. Excel will automatically apply the short time format, which doesn’t display seconds.

2. In H3, enter the formula =H2+TIME(2,0,0).
3. Copy the formula down as far as you like. I stopped at H13 (Figure C).

As you can see in Figure C, the hour component increases by two hours for the entire series. The 0 value in the minute and second argument act as placeholders so the formula doesn’t increment those two components.

You don’t have to stop with an increment of one component, you can increment all three as follows:

1. Enter 12 PM, the first value in the series, into I2.
2. Enter the formula =H3+TIME(1,15,30) in I3.
3. Copy that formula down as far as you like.
4. To see the seconds, apply the long time format (Figure D).

As you can see, each time increments by 1 hour, 15 minutes and 30 seconds. Simply alter one or all three arguments to change the series.

SEE: Learn how to hide rows and columns and use groups in a shared Excel workbook.

How to generate incremental time values using an input cell

The above formula works fine if it’s a one-time series. If you want to change those increments on the fly, you’ll need input cells, as shown in Figure E.

K2, L2 and M2 are the input cells. In addition, enter the first time value in the series in O2. You’ll change these values to change the series in column O. The formula in O3 =O2+TIME($K$2,$L$2,$M$2) uses absolute references to the input cells and is copied down as needed. To alter the series, change one or all of the input cells and the first time value in the series.

The current set of input cells begins the series at 8:00 a.m. and increments each value by two hours and 30 minutes. Don’t forget to change the format if you want the time values to display seconds.

How to generate incremental time using AutoFill

We’ve been using the TIME() function to insert time values, but using AutoFill, neither a function nor a formula is necessary. On the other hand, the solution won’t be dynamic like the previous solution with the input cells.

Let’s recreate the previous series using AutoFill:

1. Enter 8 AM into cell P2.
2. Enter 10:30:00 AM into cell P3. When using AutoFill, you need enough values to set the pattern. In this case, you only need the first two time values.
3. Select P2:P3, and double-click the Fill handle to extend the series as far as you like (Figure F). The fill handle is the tiny square in the bottom-right corner of the selection.

This example doesn’t increment the seconds value. When you do, be sure to change the format to display that component.

Incrementing time

To quickly return a series of incremental times, you need to know how Excel’s TIME() function works. By using a simple formula and specifying the time intervals in the TIME() function, you can quickly create a series of incremental time values. AutoFill is even quicker.