Thread: easy (?) subtraction problem

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

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

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

NB:will return 01/03/2003 if A1=29/2/2004

HTH
--
AP


"Tim Marsh" <tmarsh-trousers-@-take off my trousers to
reply-blueyonder.co.uk> a écrit dans le message de news:
%236J2WedoGHA.5084@TK2MSFTNGP03.phx.gbl...
> 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
>

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tim Marsh" <tmarsh-trousers-@-take off my trousers to
reply-blueyonder.co.uk> wrote in message
news:%236J2WedoGHA.5084@TK2MSFTNGP03.phx.gbl...
> 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
>
>

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" <markwalling@discussions.microsoft.com> wrote in message
news:0D2BA283-244F-43D1-B9CE-B41B82A48F67@microsoft.com...
> 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
>>
>>
>>

Mark....you got that saying all wrong! It goes like this:

"If you give a man a fish, you feed him for a day. If you teach a man to
fish, you must also teach him to lie and drink beer."

Good times to all,
TomB.


"markwalling" wrote:

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

Hi Tim,

One way:

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


---
Regards,
Norman



"Tim Marsh" <tmarsh-trousers-@-take off my trousers to
reply-blueyonder.co.uk> wrote in message
news:%236J2WedoGHA.5084@TK2MSFTNGP03.phx.gbl...
> 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
>

thank you all - job done!!

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23IWlMfeoGHA.4152@TK2MSFTNGP04.phx.gbl...
> Hi Tim,
>
> One way:
>
> =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))
>
>
> ---
> Regards,
> Norman
>
>
>
> "Tim Marsh" <tmarsh-trousers-@-take off my trousers to
> reply-blueyonder.co.uk> wrote in message
> news:%236J2WedoGHA.5084@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>
>