How to Use the Dynamic Date List Function in Excel

While Excel allows you to enter dates, it is often helpful when these dates can update automatically. The Dynamic Date List function allows you to set up cells in Excel in which your dates will update according to your parameters. 

Dynamic Date List in Excel

=start­+ROWS(exp_rng)-1-offset

For making a perfect dynamic date list, above is the formula that you can use for start and any of your additional dates. Let’s take an example to closely understand the dynamic date list.

In the excel image below, the formula used in the B5 would be:

=start+ROWS($B$5:B5)-1-offset

 

A general formula of the dynamic date list in Excel

In the image, start refers to range G4 and the offset refers to the range G5. An important thing here that you need to know is that the offset refers to the days which are before the date of start.

Conditional formatting:

Excel is all about numbers, serials, and figures that we use for multiple operations. Conditional formatting helps in performing operations on future or past dates for calculation of days.

How this formula works:

If we take an example like in the image, today is START so the function for calculations would be like this:

=TODAY() //returns current date

In the image, the start date begins with B5 which means that one can use an expanding range inside these ROWS function like this:

ROWS($B$5:B5) // returns row count

By this formula, one can access the rows count in a range. After you applied the formula, the row count starts to increase by the addition of new rows. Even if you subtract 1 from the value, there would be no incrementing from the first row.

Note: Sometimes the offset is zero or black, and if it is then the first dates are equal to the start date.

  • For the display of a weekday, the formula in C5 would be:

 

=TEXT(B5,"ddd")

  • For the month, you will change the ddd into mmm like this:

=TEXT(B5,"mmm")

 

Highlighting of the start date:

The highlighting of the start date is through the shading of conditional formatting formula which is:

=$B5=start

So, this is how one can create a dynamic list in excel by using these simple formulas.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar