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"?

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...

"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.

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.

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.

"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.

Originally Posted by joeu2004[_2_]

"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.

This is the exact solution. Thank you joeu for being so persistent with coming up with a solution. And thanks to everyone else who contributed. I really appreciate it!

Originally Posted by joeu2004[_2_]

"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.

This works exactly as intended. Thank you so much. Now I have one issue - although the date format is correctly stated, once I change it to text format, it goes into that weird coding Excel does for dates. for example, a date of "201305" in text become "41395". What I want to do is add an "F" in front of "201305" and get a result of "F201305" - this is my final product. Any ideas on how to get the text to read as a regular date?

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))

"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.