thanks for teh reply Mark. it was actually the cell formatting that was
throwing me off course. i got it to work with: -

ActiveCell.Value = DateSerial(Year(ActiveCell.Offset(0, -1)) - 1,
Month(ActiveCell.Offset(0, -1)), Day(ActiveCell.Offset(0, -1)))

but i still think there might be an easier way.... never mind - nearly
saturday!

Tim

"markwalling" wrote in message
...
DateSerial() is what i think you are looking for
--
"if you give a man a fish, you feed him for a day. if you teach a man to
fish, you feed him for a lifetime."


"Tim Marsh" wrote:

Hi All,

Its the end of a long week here and my brain has already shut-down for
the
weekend.

can someone tell me how i can subtract one year from a date. eg, if the
initial date is 19/09/2006 in cell A1 how do i calculate to get the
result
of 19/09/2005 in cell B2? i need to do this for two sets of approx. 3000
dates.

i can't think what formula or vba i would use even though it seems soooo
simple! i can't simply subtract 365 days because some might be in a leap
year.

tia,

Tim