Convert military date system to standard date system

  • Thread starter Thread starter John Weaver
  • Start date Start date
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?
 
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)
 
With the original date in A1:

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

Format as mm/dd/yy
 
=DATE(LEFT(E8,2),MID(E8,3,2),RIGHT(E8,2))

where E8 is the location of your original date. HTH Otto
 
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>!)
 
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
 
correction
=left(b3,2)&"/"mid(B3,2,2)&"/"&right(b3,2)

adjust cell address to suit.

regards
FSt1
 
Back
Top