Summary

To create a list of future times at set time intervals, you can use a simple formula that adds time to an existing start date. In the example shown, the formula in D5, copied down, is:

=B5+{4,8,12,24}/24

where 4, 8, 12, and 24 represent hours in the future. The result is 4 dates with times based on the start date in column B that spill into columns D:G.

Generic formula

=A1+{4,8,12,24}/24

Explanation 

In this example, the goal is to create 4 times in the future at set intervals, based on a given start time. The intervals are 4 hours, 8 hours, 12 hours, and 24 hours. If a start time is changed, the future dates should recalculate as needed.

How Excel handles times

In Excel, dates are serial numbers and times are fractional parts of 1 day. This means the date and time values are just regular numbers and can be summed, added, and subtracted like other numbers. For example, to add 12 hours to a given date or time, you can use a formula like this:

=A1+12/24

This works because 1 hour is 1 day divided by 24 hours (1/24), and 12 hours is half of 1 day:

=A1+12/24
=A1+0.5

Multiple intervals

In the example shown, the formula in D5 is:

=B5+{4,8,12,24}/24

This is an example of using an array constant to work with multiple values at the same time. Because the array constant {4,8,12,24} contains 4 numbers, after dividing by 24 we have:

=B5+{0.166666666666667,0.333333333333333,0.5,1}

January 1, 2022 is the serial number 44562, so after addition, we have:

{44562.1666666667,44562.3333333333,44562.5,44563}

These four values spill into the range D5:G5.

Date formatting

The display of dates in Excel is controlled by number formatting. The custom number format used in the example is:

mmm d, hh:mm

This format can be customized as needed.

Legacy Excel

In older versions of Excel without dynamic array formulas, you must enter the formula as a multi-cell array formula, or as individual formulas like:

=A1+4/24
=A1+8/24
=A1+12/12
=A1+24/24

You could also set up the column headings to contain the numerators (4,8,12,24) and use cell references instead of hardcoding the numbers into the formula.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.