Dynamic date list

To create a dynamic date list, you can use a formula that increments a start date to create and display additional dates. In the example shown, the formula in B5 is: =start+ROWS($B$5:B5)-1-offset where "start" is the named range G4, and "offset" is the named range G5. Notes: (1) the offset represents days before the start date to display in the list. (2) the shading of the start date is done with conditional formatting as described below.

April 14, 2025 · 1 min · 104 words · Emily Beck

Explanation

Dates in Excel are justserial numbers, formatted to display as dates.

From this value, we subtract 1, so the date isnot incrementedin the first row.

Next, we subtract the value in thenamed range"offset" (G5).

Excel formula: Dynamic calendar grid

The offset is simply a way to begin the list of dates earlier than the start date provided.

If offset is zero or blank, the first date in the list will equal the start date.

The formulas in B5, C5, and D5 can be copied down as many rows as desired.

Excel TODAY function

The TODAY function takes no arguments.

you could format the value returned by TODAY with a datenumber format.

Excel ROWS function

Explanation#

Explanation