Date formulas help in excel?

I'm setting up an evaluation date spreadsheet in excel. The dates are exactly 6 months and 12 months after their hire dates (not taking into account the year they were hired because we require bi-annual evaluations). I need to figure out how to use only the month and day in my formula and have the year read the current year (2008, in this case and 2009 for future spreadsheets). For example: Column A1 reads "7/16/1979". Column A2 reads "=DATE(Year(A1),Month(A1)+6,Day(A1)". This returns a date of "1/16/1980". This would be fine if I didn't need the year to be "2008" in order to use another formula for highlighting approaching dates. When I use "2008" in place of "A1" for the year, it returns a year of "1906". How can I get the formula to return the current year?

Date formulas help in excel?
in the cell you want the 6 month formula in, enter ={cell where hire date is}+182.5 example =A1+182.5





for the 12 month formula add 365 instead of 180 in the above formula.





It will automatically keep the same format with year as the format of the original hire date. From there, you can easily format the cell to be just month and day.








********************Update:***********...


In excel 2007, select the area you want to set the format of.


Select conditional formatting, then new rule, then select format only cells that contain, Choose dates occuring, then choose this month/next month etc, then set format of the rule (ie font, size, color, underline, bold etc.)





I don't think it would be much different for older versions of excel.
Reply:You could say "=DATE(YEAR(A1)+29,MONTH(A1)+6,DAY(A1))"... but that only works in this special case.
Reply:Try right clicking on the cell's you want to use and "format cells" then and use a custom date that you can specify yourself. You should be able to specify just month and day. For example:





it may be mmm-dd-yy so just delete the "yy" and leave it mmm-dd and that should work. It works the same for day then year just reverse the mmm-dd to dd-mmm. It's a simple solution.
Reply:Column A1 automatically displays a default date of 1906 until it is supplied with a value from column A2. It has nothing to do with the year 2008.





I just did a small test:





A1=7/16/1979 A2=A1+182.5


B1=A2 B2=B1+182.5





As I copied the formula down I noticed that first column had the value 00/01/1906 until I copied down the formula from the second column. I used 182.5 days to represent half a year. You would need to to use 183 days on leap years though.
Reply:The Date function only works properly with dates after 1980. 1979 will give the error you describe. Use Days360 instead and format your input cells as date.