Date Format

[ExcelSavior]

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 fo
this text is supposed to be "yy-mm", or January 2013, but excel i
automatically reading it as "01/13/2012", and the format is set a
"dd-mm". How do I change the format or text so it reads the origina
text correctly as "yy/mm" or "yyyy/mm" as opposed to "dd-mm" o
"mm/dd/yyyy"

 

[Stan Brown]

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.

 

[joeu2004]

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.

 

[joeu2004]

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

 

[Ron Rosenfeld]

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.

 

[joeu2004]

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.

 

[Ron Rosenfeld]

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

 

[joeu2004]

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.

 

[ExcelSavior]

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 an
Language
Options control panel.

My text formula does make the assumption that the year is always
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 assumptio
of
years >=2000. I merely offered a simpler implementation of __your__
assumption.

I am not quite sure why, but replies haven't been able to post. First o
all, let me clarify by saying this is in Excel 2003, which i don't thin
plays much of a role anymore in the solutions you guys are submitting
Second, joeu: I have used the =DATE(2000+DAY(A1),MONTH(A1),1) formul
and got what I wanted to work. The problem is now I need the date t
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 Exce
defaults to. Is there any way to fix this

 

[joeu2004]

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

 

[Ron Rosenfeld]

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.