Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
rostroncarlyle
 
Posts: n/a
Default To create formula to add 3 years and subtract 1 day from a date?

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rowan Drummond
 
Posts: n/a
Default To create formula to add 3 years and subtract 1 day from a date?

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
rostroncarlyle
 
Posts: n/a
Default To create formula to add 3 years and subtract 1 day from a dat

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I subtract a date from a date for a sum of total years? Paige Excel Discussion (Misc queries) 4 October 27th 05 03:10 PM
how do I subtract years and then divide crystal Charts and Charting in Excel 1 October 22nd 05 07:28 PM
How do I subtract today's date from one before 1900? Bob Martin Excel Discussion (Misc queries) 3 October 13th 05 12:27 AM
compute days between dates Ron Excel Worksheet Functions 6 May 7th 05 12:34 AM
Need Formula for Date Conversion Lyle Excel Worksheet Functions 4 March 31st 05 12:09 AM


All times are GMT +1. The time now is 04:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"