Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Looking for a formula to calculate date intervals

I am not very good at complex formulas and need to figure out a way to
calculate date intervals, say 6 months before a certain date and 2 weeks
before that date, etc.

Example: Start with Oct. 9, 2006 in one cell. Now I need to calculate 6
months before that date in another cell, then 3 months in another, then 2
weeks in another and so on. Does anyone have any suggestions? I'm on Excel
2003 and looked at the "date" formulas and just can't seem to figure out how
to get what I need.

Thanks for your help in advance!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Looking for a formula to calculate date intervals

.. Start with Oct. 9, 2006 in one cell. Now I need to calculate 6
months before that date in another cell, then 3 months in another,
then 2 weeks in another and so on.


Assume source date in A1, try these, using DATE:

6 months earlier:
In B1: =DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

3 months earlier:
In C1: =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1))

2 weeks earlier (ie 14 days earlier):
In D1: =DATE(YEAR(A1),MONTH(A1),DAY(A1)-14)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sandy - PEAK ASSIST" wrote:
I am not very good at complex formulas and need to figure out a way to
calculate date intervals, say 6 months before a certain date and 2 weeks
before that date, etc.

Example: Start with Oct. 9, 2006 in one cell. Now I need to calculate 6
months before that date in another cell, then 3 months in another, then 2
weeks in another and so on. Does anyone have any suggestions? I'm on Excel
2003 and looked at the "date" formulas and just can't seem to figure out how
to get what I need.

Thanks for your help in advance!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Looking for a formula to calculate date intervals

Thanks Max!!!! I knew it could be done, just had to ask the right person!!
I really do appreciate your help and examples!!!!

Have a great day!

Sandy

"Max" wrote:

.. Start with Oct. 9, 2006 in one cell. Now I need to calculate 6
months before that date in another cell, then 3 months in another,
then 2 weeks in another and so on.


Assume source date in A1, try these, using DATE:

6 months earlier:
In B1: =DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

3 months earlier:
In C1: =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1))

2 weeks earlier (ie 14 days earlier):
In D1: =DATE(YEAR(A1),MONTH(A1),DAY(A1)-14)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sandy - PEAK ASSIST" wrote:
I am not very good at complex formulas and need to figure out a way to
calculate date intervals, say 6 months before a certain date and 2 weeks
before that date, etc.

Example: Start with Oct. 9, 2006 in one cell. Now I need to calculate 6
months before that date in another cell, then 3 months in another, then 2
weeks in another and so on. Does anyone have any suggestions? I'm on Excel
2003 and looked at the "date" formulas and just can't seem to figure out how
to get what I need.

Thanks for your help in advance!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Looking for a formula to calculate date intervals

The preceding presumes, of course, that the source date in A1 is a real date
recognized by Excel.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Looking for a formula to calculate date intervals

You're welcome, Sandy ! cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sandy - PEAK ASSIST" wrote:
Thanks Max!!!! I knew it could be done, just had to ask the right person!!
I really do appreciate your help and examples!!!!

Have a great day!
Sandy

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
Trending Formula Results by Date sony654 Excel Worksheet Functions 0 January 2nd 06 03:33 AM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
Can someone help with a formula to calculate dates? SharonP. Excel Discussion (Misc queries) 11 August 9th 05 01:47 PM
formula for filtering and a defaulting date Sue Excel Worksheet Functions 3 July 26th 05 02:18 PM
What is the formula to calculate Age when I have Date of Birth? Katiemcgi Excel Worksheet Functions 1 November 1st 04 09:07 PM


All times are GMT +1. The time now is 07:18 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"