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.
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: This works because 1 hour is 1 day divided by 24 hours (1/24), and 12 hours is half of 1 day:
Multiple intervals
In the example shown, the formula in D5 is: 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: January 1, 2022 is the serial number 44562, so after addition, we have: 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: 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: 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
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.
