Reputation: 11

How To List All Dates Between Two Dates In Excel?

I have tried the formula to generate dates between start date and end date and it is not working properly.. I would need help here

formula = =IF($D$5+ROW(D5)>=$D$6-1,"",F5+1)

The above formula is only giving value upto 9 Nov and after that its is not working. Please help me to generate dates between start date and end date

Upvotes: 1

Answers (3)

Reputation: 4251

If you have an up-to-date version of Excel, you will have the very useful SEQUENCE() function. It generates a dynamic array of numbers.

For an inclusive range (including Start and End dates), use the formula:

=B2+SEQUENCE(1+B3-B2,,0)

For an exclusive range (excluding Start and End dates), use the formula:

=B2+SEQUENCE(B3-B2-1)

The result is a dynamic array of dates, which will grow or contract as the Start and End dates are changed.

Upvotes: 4

Reputation: 1343

You forgot to lock the Row(D5)

=IF($D$5+ROW($D$5)>=$D$6-1,"",F5+1)

Upvotes: 0

Reputation: 357

Try replacing the formulas like this

=IF($D$5+ROW(D5)-5>=$D$6-1,"",F5+1)

Upvotes: 0