Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
easy (?) subtraction problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
easy (?) subtraction problem
=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: ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
easy (?) subtraction problem
=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 ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
easy (?) subtraction problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
easy (?) subtraction problem
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
easy (?) subtraction problem
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 ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
easy (?) subtraction problem
thank you all - job done!!
"Norman Jones" wrote in message ... 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 ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
easy macro problem | Excel Programming | |||
time subtraction problem | Excel Worksheet Functions | |||
EASY - Excel VBA Problem | Excel Programming | |||
VBA macro easy problem! | Excel Programming | |||
easy problem | Excel Programming |