Date "yyyymm"

[Memphis]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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.

 

[Sam Wilson]

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

 

[Stefi]

=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:

 

[Memphis]

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

 

[Gord Dibben]

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

 

[Stefi]

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