To create formula to add 3 years and subtract 1 day from a date? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
rostroncarlyle 12/7/2005 9:56 PM PST
  Question
  I would like to be able to create a formula for an Excel worksheet using a
list of dates and then subtracting one day and adding 3 years, 2 years, 2
years 3 months, 2 years 6 months and 3 years less 1 week to their respective
columns

For example, 11.10.2005 is given date
Then 10.10.2008, 10.10.2007, 10.01.008 and 03.10.2008
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Rowan Drummond 12/7/2005 10:32 PM PST
  Answer
  If you have the Analysis Toolpak addin installed you could use the edate
function which allows you to add months to a date. For subtracting days
and weeks just use -1 or -7 etc. So if you had 11.10.2005 in A1:
=EDATE(A1,36)-1
would return 10.10.2008.

Hope this helps
Rowan

rostroncarlyle wrote:
> I would like to be able to create a formula for an Excel worksheet using a
> list of dates and then subtracting one day and adding 3 years, 2 years, 2
> years 3 months, 2 years 6 months and 3 years less 1 week to their respective
> columns
>
> For example, 11.10.2005 is given date
> Then 10.10.2008, 10.10.2007, 10.01.008 and 03.10.2008
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
rostroncarlyle 12/8/2005 3:20 PM PST
   
  Many thanks. I looked at a variety of questions and responses and have
solved my problem. Your response put me on the right track though. I now
havea column of figures that keeps changing into date mode without being
formatted as a date. How do I keep this column as numbers only, please?
It stays as numbers if I put alpha letters in front but I would prefer to use
just numbers?

"Rowan Drummond" wrote:

> If you have the Analysis Toolpak addin installed you could use the edate
> function which allows you to add months to a date. For subtracting days
> and weeks just use -1 or -7 etc. So if you had 11.10.2005 in A1:
> =EDATE(A1,36)-1
> would return 10.10.2008.
>
> Hope this helps
> Rowan
>
> rostroncarlyle wrote:
> > I would like to be able to create a formula for an Excel worksheet using a
> > list of dates and then subtracting one day and adding 3 years, 2 years, 2
> > years 3 months, 2 years 6 months and 3 years less 1 week to their respective
> > columns
> >
> > For example, 11.10.2005 is given date
> > Then 10.10.2008, 10.10.2007, 10.01.008 and 03.10.2008
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies