Friday, December 23, 2011

This is the formula to change DD.MM.YY format to MM-DD-YY

   The following is the simple formula to change the DD.MM.YY Date Format to MM-DD-YY. In 2007 and above excel versions you can change the same through the Format Option (Right Click, Format, under Number tab, click Date).


But you can't use the DD.MM.YY format in 2003 and below Excel versions for different date formats. For that you need to first change the date format to MM-DD-YY then use the format option for different date formats.



Here is the simple formula you can use to change the date format from DD.MM.YY to MM-DD-YY.



=MID(A1,4,2)&"-"&LEFT(A1,2)&"-"&RIGHT(A1,2)


Once get the required format, then copy the same and use paste special values (Right Click, Paste Special and click Values) either in the same cell or in the different cell. Now you can use the Format Option (Right click, format, under number tab, click date) for different Date Formats.


If you have any other ideas other than this, you can share as comment.

3 comments:

  1. Can Use Text to Column option - Delimited - Tab - DMY to MDY

    ReplyDelete
  2. Hi,
    The below formula for converting dd.mm.yyyy to mm-dd-yyyy was worked successfully to us for converting a large SAP report

    =MID(A1,4,2)&"-"&LEFT(A1,2)&"-"&RIGHT(A1,2)

    Thanks a lot

    Raghupathi

    ReplyDelete

Share your comment here..