On Thu, 30 Aug 2007 07:19:41 -0400, Ron Rosenfeld
wrote:

On Wed, 29 Aug 2007 15:22:00 -0700, John Galt <John
wrote:

I have a database with the date split into 2 columns with year in one (yy)
and month day (mmdd) in another. My problem is that they typed in 2007 as
"07" and Excel shows "7" for the year and they input "1201" for Dec 1st. or
"601" for June 1st. The format appears to be just General and Excel is
leaving off leading zero's. If zeros were there i could just use CONCATENATE
to assemble the date using "/" between fields. Another challenge is that they
are not all single or double digit months so i can't strip off the month,
day, year using RIGHT, LEFT, MID to reassemble in another column. The
database is 30,000 to 50,000 records. What i'm after is Month and Day for a
mail merge, i.e. "November 1st" in letter.

Seems the first thing would be to combine the values into a date that Excel
will recognize.

You can use this formula:

DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100))

and to extract the Month and Day, use the TEXT function:

=TEXT(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100)),"mmmm d")

However, that will give you November 1, not November 1st. To use an ordinal
number, you need to add on the logic. So:


=TEXT(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100)),"mmmm d")&
IF(AND(MOD(DAY(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100))),100)=11,
MOD(DAY(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100))),100)<=19),"th",
IF(MOD(DAY(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100))),10)=1,"st",
IF(MOD(DAY(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100))),10)=2,"nd",
IF(MOD(DAY(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100))),10)=3,"rd","th"))))

If you just want the month and day and don't care about the year, and if you
are using US Regional settings in Control Panel, you could use this formula:

=TEXT(--TEXT(B1,"00\/00"),"mmmm d")

And, to get the Ordinal date number:

=TEXT(DATE(A1+1900+100*(A1<=29),INT(B1/100),MOD(B1,100)),"mmmm d")&
IF(AND(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),100)=11,
MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),100)<=19),"th",
IF(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),10)=1,"st",
IF(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),10)=2,"nd",
IF(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),10)=3,"rd","th"))))
--ron

Slight change in the last formula:

=TEXT(--TEXT(B1,"00\/00"),"mmmm d")&
IF(AND(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),100)=11,
MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),100)<=19),"th",
IF(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),10)=1,"st",
IF(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),10)=2,"nd",
IF(MOD(DAY(TEXT(--TEXT(B1,"00\/00"),"mmmm d")),10)=3,"rd","th"))))


--ron