Excel, is there a simpler way to do this?

Factual Questions
1

I have a couple of charts that are dated. For a while…well, long story short I had to try quite a few formulas to get one that, at the end of the year would roll over to the next one.

Basically I set it up so one cell has a date, and then following cells increase one day at a time. But if I tired something simple like Day(cell)+1 for example, it would work, up until December 31. Then the year would roll over to 00.

I came up with this just now. It’s no big deal, but I am curious if there’s a simpler way to do this. Thanks.

=IF(AND(MONTH(B2)=12, DAY(B2)=31), DATE(YEAR(B2)+1, 1, 1), DATE(YEAR(B2), MONTH(B2), DAY(B2)+1))

2

If you have a date in a cell then the next day is found with

=B2+1

You don’t need to add a bunch of logic for month/year rollover, it’s already built in.

ETA: I am confused about what you could possibly be doing that causes the year to go to 00 when you add one day.

3

I don’t understand. Why can’t you just corner-pull down (or over) from the first dated cell? It should automatically populate all of the cells with consecutive days.

4

OK, this is strange. I know I tried it before and it didn’t work, but I just tried it again and it did. :confused:

The chart I’m using has eight columns with dates in every other column. In each of those columns there’s a date, three blank rows, another date, and so on. So I’d have to put a date in four columns and pull down, and I just want to put a date in one column.

Anyway, like I said, the method CookingWithGas gave worked for some reason even though it didn’t work before. Guess I’ll chalk it up to a glitch.

5

You may have had something quirky in the formatting, I’m not sure. I can tell you that every time I think I have found a bug in Excel it turns out to be user error :slight_smile:

6

:smiley: It could be PEBCAK, but I’m certain it’s not.