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
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:
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.
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:
- For the month, you will change the ddd into mmm like this:
Highlighting of the start date:
The highlighting of the start date is through the shading of conditional formatting formula which is:
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.