Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trending Formula Results by Date | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Can someone help with a formula to calculate dates? | Excel Discussion (Misc queries) | |||
formula for filtering and a defaulting date | Excel Worksheet Functions | |||
What is the formula to calculate Age when I have Date of Birth? | Excel Worksheet Functions |