Year Function

[viper]

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).

[Aladin Akyurek]

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.

[viper]

[viper]

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,