Exceljet

Quick, clean, and to the point

Future time intervals

Excel formula: Future time intervals
Generic formula 
=A1+{4,8,12,24}/24
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.

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.