Future time intervals

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.