+ Reply to Thread
Results 1 to 3 of 3

How change cell year dates to automatically update to new year

  1. #1
    Registered User
    Join Date
    11-24-2013
    Location
    florida
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    29

    How change cell year dates to automatically update to new year

    My SS calculates daily budget results and has 12 tabs for the months, each of which has multiple columns for the days.
    Formulas use dates in 2 of the rows to calculate results. These cells have date format as dd/mm/yyyy displayed as 1/1, 1/2, etc. like this for January:
    Month end date 1/31 1/31 1/31 1/31 1/31
    Date of month 1/1 1/2 1/3 1/4 1/5

    All of these cells in these 2 rows need to switch to year 2015 from 2014 once the system date switches to the new year. I understand the DATE format uses current year but the cells only switch if I reenter the date in each one - not automatically. Can anyone help with a function or formula that will automatically update the year across the rows in each month? Others will use the spreadsheet so an automated function is needed to kick in as each new year occurs. Ideas?

    My experience is limited with formulas and VBA so guidance would be appreciated. Thanks.
    -Ron
    ----------------------------------
    Update 1/6/2015
    Changing the cell format to just month and day didn't work either so neither it nor the F9 approach didn't change the year automatically when going from 2014 to 2015. HOWEVER...
    Entering this following formula in each cell that has a date which is year sensitive does the job:
    =DATE(TEXT(NOW(),"yyyy"),1,6) as an example where 1 is the month and 6 is the day of the month.
    I tested this by setting all dates in the SS to each day in Jan, 2014 and the system date set to January, 2014. The SS was saved and closed. The system date was then reset to January, 2015 and the SS reopened. All dates and functions responded properly to 2015.
    For some SS this may take some time to set up in all the appropriate cells but it does work and eliminates the risk and need of sending a new SS each year if you have a SS that is passed to others and they use it from one year to another (such as a budget). Hope this may help some others. -Ron
    Last edited by RonRich; 01-06-2015 at 10:08 AM. Reason: Status update

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How change cell year dates to automatically update to new year

    you need to force a recalculation just hit F9, or close open the workbook
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    11-24-2013
    Location
    florida
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    29

    Re: How change cell year dates to automatically update to new year

    Thanks Martin for guidance. The F9 didn't work. Upon closer investigation I found that if I replaced my original data entries for "Month end date" of say 1/4/2014 with just 1/4, THEN the cell would automatically go to 1/4/2015 and the calculations made correctly. I have manually changed all cells for Month End Date and Date of Month to the simplified format and all is well.

+ 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. 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
  2. Replies: 4
    Last Post: 10-17-2013, 09:59 AM
  3. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  4. I want the dates to change automatically so I can print out a year's worth.
    By MrHappyGoLucky12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2013, 09:02 PM
  5. Year and month update automatically
    By amsanborn in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-29-2010, 04:50 AM

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