< Go Back

Dynamic date list

As a new user of excel, you might be wondering about the formulas and ways to work on it. There many important things one should know about using excel. Let’s talk about the dynamic date list in excel.

  • Dynamic date list

=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. Excel ranges from simple to complex math but a single formula solves all your problems here. 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.

 

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
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar