Dates split over multi columns in worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Create a temporarly column to get the Month and Date settled, say Col C and
enter:
=IF(LEN(B2)=3,TEXT(B2,"0000"),TEXT(B2,"0000")) << where B2 if your 601 or
1201

in D2 enter:
=DATE(2000+A2,LEFT(C2,2),RIGHT(C2,2)) << where A2 is 7 (your year #)

Copy down both formulas - Finally do the Copy Paste-Special Values thing
and erase/delete Column C


HTH

Jim May
 
Jim,

Both you TEXT() functions are the same in your formula so all you need is:

=TEXT(B2,"0000")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy -- you are right -- I suppose I was a bit to focused on certain aspects
of the problem..... LOL
 
On Wed, 29 Aug 2007 15:22:00 -0700, John Galt <John
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.
Click to expand...

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
 
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
Click to expand...

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
 
Thanks all! Jim's solution got me there, and I made the same jump as Sandy at
that point. I had tried to format "####", but didn't understand the
difference and try "0000". Anyway, my letter is working & all is well, thanks
again.

PS- Ron your answer was very educational, but I didn't really need the "st"
at the end in this case. Lesson for everyone, watch your wording - you might
get what you asked for!! :)
 
Back
Top