Results 1 to 3 of 3

How change cell year dates to automatically update to new year

Threaded View

RonRich How change cell year dates to... 01-03-2015, 10:25 PM
martindwilson Re: How change cell year... 01-03-2015, 11:28 PM
RonRich Re: How change cell year... 01-05-2015, 08:29 AM
  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

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