How do I insert dates between the dates I already have?

**teozorro** · 08-16-2012, 10:35 AM

Hi all,

First-time poster, here. No, not that kind of poster. The other kind, without the blue sticky tack.

Anyhow, here's my quandary... I have a data set with a bunch of random dates listed. I sorted these dates so they're in order, however, I'd like to insert the dates that are missing in between.

That is, I have 12/8/09, then 12/12/09, and then 12/15/09. Yet, I want 12/8/09, 12/9/09, 12/10/09, etc.

How do I do this? I could obviously suffer through by hand, but I might destroy expensive things along the way.

Best,
Teozorro

**tigeravatar** · 08-16-2012, 10:44 AM

Teozorro,

Welcome to the forum!
Assuming the dates are in column A and row 1 is a header row so actual data starts on row 2, give this a try:

In cell B2 (to get the new date column started):

=MIN(A:A)

In cell B3 and copied down until you start getting blanks

=IF(B2="","",IF(B2+1>MAX(A:A),"",B2+1))

Then format the cells as date.

**teozorro** · 08-16-2012, 10:57 AM

Thanks so much, tigeravatar! Actually, this didn't work out for my particular data set. I don't have any spaces in between the dates in my rows, so my set goes straight from 12/8/09 to 12/11/09 without any break.

But this did inspire me. In a separate sheet, I used your first command, but then I simplified the second one to

=B2+1

in date format. Then I copied this down and just merged my two tables.

Wonderful! Thanks so much for your speedy guidance.

**tigeravatar** · 08-16-2012, 11:22 AM

You're welcome, and I'm glad you got a working solution

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save