Date Format

Originally Posted by joeu2004[_2_]

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

I am not quite sure why, but replies haven't been able to post. First of all, let me clarify by saying this is in Excel 2003, which i don't think 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) 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?

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

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.