+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    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. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    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. #3
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

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

    Quote Originally Posted by Phuocam View Post
    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. #4
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

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

    I've uploaded a sample if that will help.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

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

    Change to:

    =DATE(YEAR($V$1),ROWS($B$4:B4),1)
    Last edited by Phuocam; 11-24-2020 at 08:37 PM.

  6. #6
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

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

    Quote Originally Posted by Phuocam View Post
    Change to:

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Year Stamp if data is entered in a cell
    By anilpatni1234 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-05-2019, 10:36 AM
  2. Replies: 8
    Last Post: 07-20-2018, 11:52 AM
  3. Replies: 1
    Last Post: 04-20-2015, 12:11 PM
  4. How change cell year dates to automatically update to new year
    By RonRich in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2015, 08:29 AM
  5. Automatically update year interval cycles from year to year
    By trumptight in forum Word Programming / VBA / Macros
    Replies: 8
    Last Post: 08-21-2014, 10:38 PM
  6. Replies: 4
    Last Post: 10-17-2013, 09:59 AM
  7. Replies: 2
    Last Post: 08-09-2011, 09:54 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1