Need Formula to reformat dates (ex: 10203 to 02/03/01)

[BeyondMethod]

Hello all,

I need help trying to figure out some formula for Excel that can rearrange a series of numbers into a date. I have a spreadsheet in which the dates are entered as yearmonthday (YYMMDD), but for the year it doesn't include the zero.

For example:

12/03/99 comes out as 991203
12/03/00 comes out as 1203
12/03/01 comes out as 11203

Does anyone know of a formula I could use in Excel (or some other way in Excel) so that I can rearrange the numbers to make them actual dates in MM/DD/YY format? I'm kind of new at this, any help would be appreciated.

Thanks!

 

[Smitty]

Welcome to the Board!

This is a pretty frustrating thing to deal with, but first question would be is are your dates entered like that by hand, or are you getting them that way from another source?

I.E. Our AS400 system spits dates out in the format that you showed; I bounce the data through an Access query each week to fix it, but that may not be a solution for you if you or your users are hand entering the dates that way.

See: http://www.cpearson.com/excel/datetime.htm for a bit more on how Excel deals with dates.

Smitty

 

[BeyondMethod]

The dates actually aren't from hand. They come out like that after we run a query on Access. Maybe I could do something similar to you by using Access to fix it. I'm even more new at Access though, so I'm still learning there too.

 

[Smitty]

If you're already using Access then an additional step to your query is probably simplest.

I have a table of all of our publication dates from 1997 through 2006 that looks something like:

Book1
	A	B	C	D
1	SystemDate	Date
2	40107	01/07/04
3	40114	01/14/04
4	40121	01/21/04
5	40128	01/28/04
6	40204	02/04/04
7	40211	02/11/04
8	40218	02/18/04
9	40225	02/25/04
10	40303	03/03/04
Sheet1

In your query, tie system date to the date field in your query table. Then replace the formatted Date with System Date in the query. Access will do the rest from there.

Hope that helps,

Smitty

 

[GorD]

If the above dosen't work, you can use the formula:

=IF(LEN(A2)=6,A2,IF(LEN(A2)=5,"0"&A2,IF(LEN(A2)=4,"00"&A2))) to restore all dates to 6 digits. Then copy and paste special, values over these new dates. Then select the new dates, and use data - text to columnsa nd specify dates YMD format. This will then give you your date in a standard format and you can use - format, cell, date to display it in whatever format you like.

Book1
	A	B	C	D	E
1	Orig	Formulaapplied			Texttocolsthendateformatdmmmyy
2	991203	991203			03/Dec/99
3	1203	001203			03/Dec/00
4	11203	011203			03/Dec/01
Sheet1

Hope this helps

 

[BeyondMethod]

Thanks SO MUCH for the help so far. I won't be able to try it out till tomorrow at work, but I'll let you know how it goes.

Thanks again, I appreciate it!

 

[shaikeelim]

@ Smitty:

I am new to this website. I have a similar issue, but the only difference is my dates are hand written and there are more than 1000 rows of data. How can change the format of 090997 (mm/dd/yy) to 09 sep 97? and similarly for all the other dates???

Please help.

 

[shaikeelim]

@All:

Just found out a way to convert the number format to a date format:

select the column that you want to convert the format, go to Data - Text to Columns.... and highlight Delimited and click next, click next, and change the column data format from General to Date and hit finish.

Now go to format-cells and select date as category and type as you wanted and hit Ok. This will change your number format to the date format.

I hope it helps.