Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Formula: Date plus 3 months
Hiya, I have a date in cell A1 and I want to know a formula which will
calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#2
|
|||
|
|||
Hi Carly
One way: =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)) -- Best Regards Leo Heuser Followup to newsgroup only please. "Carly" skrev i en meddelelse ... Hiya, I have a date in cell A1 and I want to know a formula which will calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#3
|
|||
|
|||
Thats done the trick; many thanks Leo!
-- Carly "Leo Heuser" wrote: Hi Carly One way: =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)) -- Best Regards Leo Heuser Followup to newsgroup only please. "Carly" skrev i en meddelelse ... Hiya, I have a date in cell A1 and I want to know a formula which will calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#4
|
|||
|
|||
You're welcome, Carly, and thanks for the feedback :-)
LeoH "Carly" skrev i en meddelelse ... Thats done the trick; many thanks Leo! -- Carly |
#5
|
|||
|
|||
Date functions in XL are a bit problematic.
With May 31, 2005 in A1 and with =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)) in B1 the return date is March 3, 2005 I don't think that's what the OP really wants. Using the edate from the analysis toolpack as someone else suggested may be better. "Leo Heuser" wrote in message ... Hi Carly One way: =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)) -- Best Regards Leo Heuser Followup to newsgroup only please. "Carly" skrev i en meddelelse ... Hiya, I have a date in cell A1 and I want to know a formula which will calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#6
|
|||
|
|||
With the Analysis Toolpak add-in installed use
=edate(a1,-3) "Carly" wrote: Hiya, I have a date in cell A1 and I want to know a formula which will calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#7
|
|||
|
|||
Thanks Duke. That one would be perfect, but I don't have the Analysis Toolpak
:( Any ideas of a work around? -- Carly "Duke Carey" wrote: With the Analysis Toolpak add-in installed use =edate(a1,-3) "Carly" wrote: Hiya, I have a date in cell A1 and I want to know a formula which will calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#8
|
|||
|
|||
Carly -
The Analysis Toolpak is standard with Excel. Go to ToolsAdd-ins and check it to isntall it. It gives you access to quite a variety of powerful functions & is well woorth while. "Carly" wrote: Thanks Duke. That one would be perfect, but I don't have the Analysis Toolpak :( Any ideas of a work around? -- Carly "Duke Carey" wrote: With the Analysis Toolpak add-in installed use =edate(a1,-3) "Carly" wrote: Hiya, I have a date in cell A1 and I want to know a formula which will calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#9
|
|||
|
|||
I agree - It is very powerful - why isn't part of the standard functions -
is this due to the historical versions when the add-in was first developed? "Duke Carey" wrote in message ... Carly - The Analysis Toolpak is standard with Excel. Go to ToolsAdd-ins and check it to isntall it. It gives you access to quite a variety of powerful functions & is well woorth while. "Carly" wrote: Thanks Duke. That one would be perfect, but I don't have the Analysis Toolpak :( Any ideas of a work around? -- Carly "Duke Carey" wrote: With the Analysis Toolpak add-in installed use =edate(a1,-3) "Carly" wrote: Hiya, I have a date in cell A1 and I want to know a formula which will calculate this date minus 3 months, in cell A2. I've tried a simple "=A1-3month" but this doesn't work. Any ideas? -- Carly |
#10
|
|||
|
|||
=A1-90 as in 90 (or whoever many) days? -- username ------------------------------------------------------------------------ username's Profile: http://www.msusenet.com/member.php?userid=1433 View this thread: http://www.msusenet.com/t-1870443952 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
Return a date 6 months from a date in another cell | Excel Worksheet Functions | |||
dynamic year to date formula | Excel Worksheet Functions | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) | |||
Need number of months or weeks passed from formula | Excel Worksheet Functions |