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
Bookmarks