|
Dates split over multi columns in worksheet
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. |
Dates split over multi columns in worksheet
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 "John Galt" 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. |
Dates split over multi columns in worksheet
Sandy -- you are right -- I suppose I was a bit to focused on certain aspects
of the problem..... LOL "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Jim May" wrote in message ... 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 "John Galt" 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. |
Dates split over multi columns in worksheet
|
Dates split over multi columns in worksheet
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 |
Dates split over multi columns in worksheet
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!! :) "Ron Rosenfeld" wrote: 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 |
| All times are GMT +1. The time now is 03:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com