Date "yyyymm"

  • Thread starter Thread starter Memphis
  • Start date Start date
M

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
 
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
Click to expand...

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.
 
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
 
=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:
 
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
 
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
Click to expand...

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