Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Format
I'm working on a data file that has the date input in the wrong format. In the date column, it shows "13-Jan". The original format intention for this text is supposed to be "yy-mm", or January 2013, but excel is automatically reading it as "01/13/2012", and the format is set as "dd-mm". How do I change the format or text so it reads the original text correctly as "yy/mm" or "yyyy/mm" as opposed to "dd-mm" or "mm/dd/yyyy"?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
On Mon, 6 Aug 2012 20:08:57 +0000, ExcelSavior wrote:
I'm working on a data file that has the date input in the wrong format. In the date column, it shows "13-Jan". The original format intention for this text is supposed to be "yy-mm", or January 2013, but excel is automatically reading it as "01/13/2012", and the format is set as "dd-mm". How do I change the format or text so it reads the original text correctly as "yy/mm" or "yyyy/mm" as opposed to "dd-mm" or "mm/dd/yyyy"? Highlight the cells in question and press Ctrl-1 to open the Format Cells dialog. On the Number tab, select Date. If none of the formats there is what you want, select Custom and you can type in your exact format. Unfortunately Excel doesn't seem to give any way to set the default date format fr new workbooks, so for every single workbook we have to format date cells manually unless we like Excel's own choice. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
"ExcelSavior" wrote:
I'm working on a data file that has the date input in the wrong format. In the date column, it shows "13-Jan". The original format intention for this text is supposed to be "yy-mm", or January 2013, but excel is automatically reading it as "01/13/2012", and the format is set as "dd-mm". How do I change the format or text so it reads the original text correctly as "yy/mm" or "yyyy/mm" as opposed to "dd-mm" or "mm/dd/yyyy"? As you may understand, the problem is with the form of the input and how Excel interprets it, not with the format of the cell. That is affected by the Regional and Language Options control panel. No change to the format of the cell will affect that. You have several options to remedy the problem. Unfortunately, you neglect to say exactly what is the form of the data file, how you are inputing it, and what version of Excel you are using. So it is difficult to be specific. The most obvious option is to change the short-date form in the R&LO control panel temporarily. That is probably not a viable solution. First, it is tedious to do. Second, it might not even be possible if you are on a shared computer. The second most obvious option is to let the misinterpretation happen, then correct it with formulas later. Note that some input of the form yy-mmm, namely when yy exceeds the last day of mmm of the current year, will not be interpreted at all; it will be treated as text. If the dates are in A1:A1000, you might do the following: 1. Enter the following formula in X1 (for example) and copy down through X1000: =IF(ISTEXT(A1),--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)), DATE(DAY(A1),MONTH(A1),1)) 2. Copy X1:X1000. Paste-special-value into A1:A1000. Delete X1:X1000. 3. Format A1:A1000 as you wish. Note-1: Replace MID(A1,1+FIND("-",A1),99) with simply RIGHT(A1,3) if we can assume that the month abbreviation is always 3 characters. Note-2: The MID&1&LEFT expression assumes that your long-date form is month day, year. See your Regional and Lanaguage Options control panel settings, and change the formula according if necessary. I would prefer to use an expression using the DATE function. But I discovered that DATE(13,1,1) is (mis)interpreted as 1/1/1913 even though 1/1/13 is interpreted as 1/1/2013. A third option -- to correct the data at input time -- does not seem to work. Ostensibly, we would use Import External Data wizard, selecting the YMD input form in the last dialog box. But that fails to interpret yy-mmm as year-month as intended. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
Errata.... I wrote:
=IF(ISTEXT(A1),--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)), DATE(DAY(A1),MONTH(A1),1)) [....] Note-2: The MID&1&LEFT expression assumes that your long-date form is month day, year. See your Regional and Lanaguage Options control panel settings, and change the formula according[ly] if necessary. I would prefer to use an expression using the DATE function. But I discovered that DATE(13,1,1) is (mis)interpreted as 1/1/1913 even though 1/1/13 is interpreted as 1/1/2013. Wasn't thinking clearly. The fluke with DATE(13,...) caught me by surprise. But it applies to the value-if-false expression as well. I guess we must write: =IF(ISTEXT(A1),--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)), --(MONTH(A1) & "/1/" & DAY(A1)) And that assumes that your short-date form is month/day/year. PS: I am unhappy with this method. Hopefully someone will think of something that works independent of regional settings. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
On Mon, 6 Aug 2012 20:08:57 +0000, ExcelSavior wrote:
I'm working on a data file that has the date input in the wrong format. In the date column, it shows "13-Jan". The original format intention for this text is supposed to be "yy-mm", or January 2013, but excel is automatically reading it as "01/13/2012", and the format is set as "dd-mm". How do I change the format or text so it reads the original text correctly as "yy/mm" or "yyyy/mm" as opposed to "dd-mm" or "mm/dd/yyyy"? As joeu as pointed out, this is a difficult problem. Here is another solution: Change the extension of the data file to .txt Open the file in Excel. The Data Import Wizard should appear. With regard to the column with the malformed dates, select to import this as TEXT. You should then see the unaltered data in that column; and all of the entries should be text. You can then convert it into a string that Excel will recognize as a date; convert it to a true date; and format it as yy-mmm. For example, with the text in column A: =--(RIGHT(A1,3) &" 1, " &LEFT(A1,FIND("-",A1)-1)+2000) or =--(1 & RIGHT(A1,3) &LEFT(A1,FIND("-",A1)-1)+2000) will convert it to a date equivalent. You can then format the result as yy-mmm. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
"Ron Rosenfeld" wrote:
As joeu as pointed out, this is a difficult problem. Here is another solution: [....] =--(RIGHT(A1,3) &" 1, " &LEFT(A1,FIND("-",A1)-1)+2000) Well, if we're going to make the assumption that all dates are in the year 2000 or later, it is not difficult at all to provide a region-independent solution. Since we're making unsubstantiated assumptions, let's assume the dates are in the years 2001 through 2028. Then the following should work, assuming that yy-mmm data are already input and interpreted as dd-mmm of the current year (i.e. numeric Excel dates; the display format does not matter). Enter the following formula into X1 and copy down through X1000: =DATE(2000+DAY(A1),MONTH(A1),1) Copy X1:X1000 and use paste-special-value to overwrite A1:A1000. Delete X1:X1000. Format A1:A1000 any way you wish. |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
Quote:
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
On Tue, 7 Aug 2012 12:30:26 -0700, "joeu2004" wrote:
"Ron Rosenfeld" wrote: As joeu as pointed out, this is a difficult problem. Here is another solution: [....] =--(RIGHT(A1,3) &" 1, " &LEFT(A1,FIND("-",A1)-1)+2000) Well, if we're going to make the assumption that all dates are in the year 2000 or later, it is not difficult at all to provide a region-independent solution. Since we're making unsubstantiated assumptions, let's assume the dates are in the years 2001 through 2028. Then the following should work, assuming that yy-mmm data are already input and interpreted as dd-mmm of the current year (i.e. numeric Excel dates; the display format does not matter). Enter the following formula into X1 and copy down through X1000: =DATE(2000+DAY(A1),MONTH(A1),1) Copy X1:X1000 and use paste-special-value to overwrite A1:A1000. Delete X1:X1000. Format A1:A1000 any way you wish. Excellent point, given the assumption of year being 2001-2028. On the other hand, if the 2-digit year should be interpreted as any other 2-digit year being entered with the current default settings for Windows of being interpreted as being 1930-2029, then one could modify my text import method to use the formula: =--(RIGHT(A1,3)&" 1, "&LEFT(A1,FIND("-",A1)-1)+2000-100*(--LEFT(A1,FIND("-",A1)-1)29)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
"Ron Rosenfeld" wrote:
On the other hand, if the 2-digit year should be interpreted as any other 2-digit year being entered with the current default settings for Windows of being interpreted as being 1930-2029, then one could modify my text import method to use the formula: =--(RIGHT(A1,3)&" 1, "&LEFT(A1,FIND("-",A1)-1)+2000-100*(--LEFT(A1,FIND("-",A1)-1)29)) And what is the benefit of that over either of my previous suggestions, to wit: For text date: =--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)) For numeric date: =--(MONTH(A1) & "/1/" & DAY(A1)) Besides being shorter, my suggestions are not limited to the default interpretation of yy<30, which can be altered in the Regional and Language Options control panel. My text formula does make the assumption that the year is always 2 digits, but the month might not always be 3 characters. You make the opposite assumptions (more likely). Eliminating both assumptions, I would write: =--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,FIND("-",A1)-1)) Still simpler and more flexible, IMHO. No matter. My only previous point was: you introduced the assumption of years =2000. I merely offered a simpler implementation of __your__ assumption. |
#11
|
|||
|
|||
Quote:
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
"ExcelSavior" wrote:
I have used the =DATE(2000+DAY(A1),MONTH(A1),1) formula and got what I wanted to work. The problem is now I need the date to show as a date in text format, i.e. 201301 needs to be 201301 when changed to text format, not the weird coding for dates that Excel defaults to. Is there any way to fix this? I don't know what "weird coding" you are referring to. You can format the cell(s) or column any way you wish. In this case, it appears that you want the Custom format yyyymm. Select the cell(s) or column, right-click and click on Format Cells, then the Number tab, then Custom. Enter yyyymm into the Type field, and click on OK. However, the date will remain numeric, not literally text. I suspect that it is what you meant. But if you truly want text -- that ISTEXT(B1) should return TRUE -- you can do the following: =TEXT(DATE(2000+DAY(A1),MONTH(A1),1),"yyyymm") |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
On Wed, 8 Aug 2012 00:00:26 -0700, "joeu2004" wrote:
And what is the benefit of that over either of my previous suggestions, to wit: For text date: =--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)) For numeric date: =--(MONTH(A1) & "/1/" & DAY(A1)) As you had originally posted at the time I downloaded the NG: =IF(ISTEXT(A1),--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)), DATE(DAY(A1),MONTH(A1),1)) I offered another approach, which obviated the need to first determine if the data being processed is text or not. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |