| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date "yyyymm"
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date "yyyymm"
On Tue, 15 Dec 2009 06:31:02 -0800, 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 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date "yyyymm"
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date "yyyymm"
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date "yyyymm"
=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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date "yyyymm"
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date "yyyymm"
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 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date "yyyymm"
On dec. 16, 21:24, Memphis 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert numbers to date: "586" to read "May 1986" | Excel Worksheet Functions | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date? | Excel Programming | |||
| Converting yyyymm and yyyymmdd to "mmm dd, yyyy" | Excel Discussion (Misc queries) | |||
| Why does the date change from "January 07" to a numercial value of "39038 " | Excel Programming | |||
Linear Mode
