Convert military date system to standard date system

J

John Weaver

A former employee recorded all dates as 090917 (yymmdd, September 17, 2009)
as text format.

How do I convert this format to 09/17/09, mm/dd/yy format?
 
D

Dave Peterson

If all those entries are in a single column, you could:
Select the column
Data|Text to columns (xl2003 menus)
Fixed width (but don't have any field separators)
choose Date (ymd order)
and finish up.

I'd give the range an unambiguous date format (that isn't used) so that you can
see if the dates are what they're supposed to be.

mmmm dd, yyyy
(for example)
 
N

Niek Otten

With the original date in A1:

=DATE(2000+MID(A1,3,2),LEFT(A1,2),RIGHT(A1,2))

Format as mm/dd/yy
 
O

Otto Moehrbach

=DATE(LEFT(E8,2),MID(E8,3,2),RIGHT(E8,2))

where E8 is the location of your original date. HTH Otto
 
G

Gary''s Student

=DATE("20" & LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)) and format it as you like.
 
D

Dave Peterson

or

=DATE(2000+left(A1,2),mid(A1,3,2),RIGHT(A1,2))

(I don't think you noticed that it was in yymmdd order--and that 09 in both
spots didn't help <vbg>!)
 
F

FSt1

hi
since the year comes first in your example, it might require some flipping
around. you might try this in a helper(blank) column added next to the
problems dates. then insert this fomula in the top cell and copy down the
helper column.
=MID(B3,3,2)&"/"&RIGHT(B3,2)&"/"&LEFT(B3,2)
you could then copy the helper column and paste special values over the
problem dates and delete the helper column.

regards
FSt1
 
F

FSt1

correction
=left(b3,2)&"/"mid(B3,2,2)&"/"&right(b3,2)

adjust cell address to suit.

regards
FSt1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top