Posted 04 February 2011 - 05:23 AM
Before using NOW you need to be aware that this is a volatile function. This means that every time you make any change to anything in your spreadsheet, the NOW function is recalculated (it fetches the system time and date again), then all the cells which depended on it will also be recalculated.
TODAY (which returns the date without the time element) would make just as much sense for the use you are putting it to here, but again this is volatile.
Since the year only changes once every 365 days or so (I think), it seems a bit daft to have a spreadsheet calculating hundreds or thousands of cells every time you touch it. Sledgehammer > walnut.
Just put the year as a value (2011) in a single cell and reference that in your formulae instead.
Better still, as a learning experience, find out about using defined names and simply have a name such as "CurrentYear" which you can then either point at the cell you have that value in or explicitly defined as a constant value. In this case I would tend to go for the cell reference approach as the year is then visible on the spreadsheet so it is obvious if it is incorrect, and easier to change later. You can also name the whole of column K as "BirthYear" and then your formula in column L cells would read CurrentYear-BirthYear. This is easily understood, it is obvious what it is calculating and you don't need to worry about those $ signs to force relative and absolute references in the right places (CurrentYear points at a single fixed cell, BirthYear points at a whole column, so Excel uses an implicit intersection to give you the value in that column from the row you are currently working on).
Hope this helps
Due to global warming, eskimos now have more than 20 words for water John O'FarrellProfessional geek, consultant and trainer.
MCT, MCSA, MMI, MBMSS, CWNA, COS (I like to have lettuce after my name)
My personal blog - Getting IT right