I have a worksheet were the dates are displayed as 200901 (yyyymm --- no
slash) in column A.
I need to add a formula to cells in colum C that will only substract any
given number of months from this date, such as 200901 minus 13 months will
equal 200811. Now, on the same boat, I would like to show the new date
displayed as 11/01/2008.

Thank you

On Tue, 15 Dec 2009 06:31:02 -0800, Memphis <(E-Mail Removed)>
wrote:

>I have a worksheet were the dates are displayed as 200901 (yyyymm --- no
>slash) in column A.
>I need to add a formula to cells in colum C that will only substract any
>given number of months from this date, such as 200901 minus 13 months will
>equal 200811. Now, on the same boat, I would like to show the new date
>displayed as 11/01/2008.
>
>Thank you

I guess I don't understand your math.

I would interpret 200901 to be 2009 Jan and, since the day is not specified, to
be the first of Jan. In other words, 200901 --> Jan 1st, 2009

How do you subtract 13 months from that and get Nov 1st, 2008?

If I subtract 13 months, I would think the correct answer should be Dec 1st,
2007.

If my assumptions are incorrect, please clarify.

If your examples are incorrect, then try:

A1: 200901
B1: -13 (months to add/subtract)
C1: =DATE(LEFT(A1,4),RIGHT(A1,2)+B1,1)

Format C1 as mm/dd/yyyy
--ron

First off, 13 months subtracted from January 2009 is not November 2008 (nor
would 3 months be if the 13 were a typo). Staying with you 13 month number,
here is the formula...

=DATE(LEFT(A1,4),RIGHT(A1)-13,1)

You can replace the 13 with whatever you want (or use a cell reference in
its place to make the value changeable). As for how you want to display the
resulting date, just format the cell as desired.

--
Rick (MVP - Excel)


"Memphis" <(E-Mail Removed)> wrote in message
news:EDC284AB-F8B9-4287-A25B-(E-Mail Removed)...
>I have a worksheet were the dates are displayed as 200901 (yyyymm --- no
> slash) in column A.
> I need to add a formula to cells in colum C that will only substract any
> given number of months from this date, such as 200901 minus 13 months will
> equal 200811. Now, on the same boat, I would like to show the new date
> displayed as 11/01/2008.
>
> Thank you

If 200901 was in cell A1, and 13 in B1, this formula:

=DATE(LEFT(A1,4)-INT(B1/12),RIGHT(A1,2)-B1+12*INT(B1/12),1)

should do what you want. I make it 01/12/07 (or 12/01/07 for America...) not
november 08 though.

Sam


"Memphis" wrote:

> I have a worksheet were the dates are displayed as 200901 (yyyymm --- no
> slash) in column A.
> I need to add a formula to cells in colum C that will only substract any
> given number of months from this date, such as 200901 minus 13 months will
> equal 200811. Now, on the same boat, I would like to show the new date
> displayed as 11/01/2008.
>
> Thank you

=DATE(YEAR(A2),MONTH(A2)-13,DAY(A2))
and format the result cell to meet your needs! But 200901 - 13 months
results in 200712 and not 200811. If you want 200711 then
=DATE(YEAR(A2),MONTH(A2)-14,DAY(A2))

--
Regards!
Stefi



„Memphisâ€� ezt Ã*rta:

> I have a worksheet were the dates are displayed as 200901 (yyyymm --- no
> slash) in column A.
> I need to add a formula to cells in colum C that will only substract any
> given number of months from this date, such as 200901 minus 13 months will
> equal 200811. Now, on the same boat, I would like to show the new date
> displayed as 11/01/2008.
>
> Thank you

Thank you guys for the replies, and my apologies for not replying back
sooner. I could have sworn that I selected "Notify me of replies".

I agree with all of you that the resulting date should be 12/2007 ;-) my
bad...

Thank you for your help on this.

Regards.

Memphis

Given the flakiness of the web interface I would come back periodically to
look.

You may never get notified of a reply.


Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 12:24:01 -0800, Memphis
<(E-Mail Removed)> wrote:

>Thank you guys for the replies, and my apologies for not replying back
>sooner. I could have sworn that I selected "Notify me of replies".
>
>I agree with all of you that the resulting date should be 12/2007 ;-) my
>bad...
>
>Thank you for your help on this.
>
>Regards.
>
>Memphis

On dec. 16, 21:24, Memphis <Memp...@discussions.microsoft.com> wrote:
> Thank you guys for the replies, and my apologies for not replying back
> sooner. I could have sworn that I selected "Notify me of replies".
>
> I agree with all of you that the resulting date should be 12/2007 ;-) my
> bad...
>
> Thank you for your help on this.
>
> Regards.
>
> Memphis

See my thread "not receiving notifying e-mails"! I followed Ms-Exl-
Learner's suggestions and it solved my notification problem.
Stefi