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

BeyondMethod

New Member
Joined
Feb 22, 2004
Messages
4
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Feb 22, 2004
Messages
4
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

Legend
Joined
May 15, 2003
Messages
29,536
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
ABCD
1SystemDateDate
24010701/07/04
34011401/14/04
44012101/21/04
54012801/28/04
64020402/04/04
74021102/11/04
84021802/18/04
94022502/25/04
104030303/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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

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
ABCDE
1OrigFormulaappliedTexttocolsthendateformatdmmmyy
299120399120303/Dec/99
3120300120303/Dec/00
41120301120303/Dec/01
Sheet1


Hope this helps
 

BeyondMethod

New Member
Joined
Feb 22, 2004
Messages
4
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

New Member
Joined
Sep 9, 2011
Messages
2
@ 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

New Member
Joined
Sep 9, 2011
Messages
2
@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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,101
Members
425,453
Latest member
bince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top