Year Function

viper

Active Member
Joined
Feb 15, 2002
Messages
382
I have never used this one before so I really have no clue how to do this. I have 12 hidden cells that contain a month ending date, what I need is to write a permanate code that will decrease each of the 12 cells date by one based on the current year. Example(year of 2002 Feb. 27, 2002 is contained in say cell (B12), in 2003 that same day will be Feb. 26, 2003) So on January 1, 2003 each of the cells will need to be decreased by one. Any help is greatly appreicated(I know spelled wrong).
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
On 2002-02-23 17:51, viper wrote:
I have never used this one before so I really have no clue how to do this. I have 12 hidden cells that contain a month ending date, what I need is to write a permanate code that will decrease each of the 12 cells date by one based on the current year. Example(year of 2002 Feb. 27, 2002 is contained in say cell (B12), in 2003 that same day will be Feb. 26, 2003) So on January 1, 2003 each of the cells will need to be decreased by one. Any help is greatly appreicated(I know spelled wrong).

Not sure I understand your question, but it seems you want to add 1 year to a given date:

If so, use either


=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

or

=EDATE(A1,12) [ this one requires the Analysis Toolpak ]

where A1 houses a date.

If this is not what you want, please elaborate.
 
Upvote 0
Actually your code given might work for I want. It's not the year I want to add to, our fiscal months end on Wednesdays each month, 4 are 5 week months and the rest are 4 week months. Our months end on the same day each year but the date is one day less. Like our month this year(2002) will be ending on 2/27/02, but next year(2003) that date will be 2/26/03. So, I just need to decrease the month ending dates I have hidden by one next year. I have the date 2/27/02 hidden on my spreadsheet, but next year on January when the computers date is 2003, that cell will need to be changed to 2/26/03. So, I think I can re-write your code to decrease the day when the year = 2003. If not please let me know different.

Thanks,
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top