# Update all dates to the year that is entered in a cell

1. ## Update all dates to the year that is entered in a cell

I keep up with payments made each month using a basic Excel sheet. The rows B4:B15 are labeled 1/1/2020, 2/1/2020, ... 12/1/2020.

I would like to have it so all the dates from B4:B15 automatically update and change their current year to whatever year I have entered in cell V1, so I don't have to go to each cell and change it manually. I know it's not a big deal for just 12 cells, but I actually have this duplicated many times over, and well, I'm sure Excel can handle this automation. I'm trying different formulas, but not having much luck. Few things I keep trying keeps giving me the dreaded date of 1/1/1905. I have a feeling I'm making this more complicated than it probably is.

Any suggestions?

2. ## Re: Update all dates to the year that is entered in a cell

Try this in B4:

=DATE(\$V\$1,ROWS(\$B\$4:B4),1)

3. ## Re: Update all dates to the year that is entered in a cell

Originally Posted by Phuocam
Try this in B4:

=DATE(\$V\$1,ROWS(\$B\$4:B4),1)
Thanks, but that formula fills horizontally, and I need it to fill vertically for the column B4:B15. Also, every cell it filled in has the #NUM! error

4. ## Re: Update all dates to the year that is entered in a cell

I've uploaded a sample if that will help.

5. ## Re: Update all dates to the year that is entered in a cell

Change to:

=DATE(YEAR(\$V\$1),ROWS(\$B\$4:B4),1)

6. ## Re: Update all dates to the year that is entered in a cell

Originally Posted by Phuocam
Change to:

=DATE(YEAR(\$V\$1),ROWS(\$B\$4:B4),1)
That did it, thank you very much!!

There are currently 1 users browsing this thread. (0 members and 1 guests)