Convert military date system to standard date system

[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?

[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)

Post by 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?

[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
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Post by 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?

[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>!)

Post by Niek Otten
=DATE(2000+MID(A1,3,2),LEFT(A1,2),RIGHT(A1,2))
Format as mm/dd/yy
--
Kind regards,
Niek Otten
Microsoft MVP - Excel

Post by 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?

[Otto Moehrbach]

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

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

Post by 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?

[Gary''s Student]

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

[David Biddulph]

=DATE(2000+LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)) and format accordingly
--
David Biddulph

Post by 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?

[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

Post by 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?

[FSt1]

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

adjust cell address to suit.

regards
FSt1

Post by 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

Post by 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?