In excel, date shows as mdyyyy, want as mm-dd-yyyy

Question

Hello I have an excel file that has a date field but what is happening is the date in the spreadsheet shows as mdyyyy (example 5271960 would be 05-27-1960). How does one convert the field in excel?

Answer 1

Excel allows to pre-scribe a user defined format for a cell as:

mm-dd-YYYY             >>       12-13-2014
mmm-dd-YYYY            >>      XII-13-2014
mmmm-dd-YYYY           >> december-13-2014

Answer 2

If 5271960 is an example of how your dates are showing at the moment then you would use this formula in another cell to get it to 05-27-1960 if this date was in cell A2

=0&(LEFT(A2,1))&"-"&(MID(A2,2,2))&"-"&(RIGHT(A2,4))

Unfortunately, This formula will only work if there are 7 numbers in your 'date' column. If you have 8 Numbers (such as 15271960) you would use this formula in another cell..

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

Hope this helps.

Answer 3

If the cell is a genuine Date, then use the Format Cells... Dialog:

Answer 4

Right-click on the cell and choose Format Cells.... Here you can either choose an existing Date format, or Custom format it to suit your needs. Settings include:

• For days:

  • d: 9 -> 9, 17 -> 17, ...

  • dd: 9 -> 09, 17 -> 17, ...

  • ddd: Day of the Week (Mon, Tue, ...)

  • dddd: Day of the Week (Monday, Tuesday, ...)

• For months:

  • m: 9 -> 9, 12 -> 12, ...

  • mm: 9 -> 09, 12 -> 12, ...

  • mmm: Month (Jan, Feb, ...)

  • mmmm: Month (January, February, ...)

• For years:

  • y: 2014 -> 14, 2017 -> 17, ...

  • yy: 2014 -> 14, 2017 -> 17, ...

  • yyy: 2014 -> 2014, 2017 -> 2017, ...

  • yyyy: 2014 -> 2014, 2017 -> 2017, ...

Similar formatting holds for time stamps as well.

Answer 5

I suspect your date field is not containing a "real" date but just the string of numbers you show. If it does contain a "real" date, then you can merely custom format it however you wish.

If it contains the value 5271960 or similar, then first convert it to a real date using this formula (assuming your pseudodate is in A1):

=DATE(MOD(A1,10^4),INT(A1/10^6),MOD(INT(A1/10^4),100))

Then custom format the cell to show the date as you prefer.

Another option, if you are only going to be dealing with Excel on machines that are using the US format in the Windows Regional Settings: MDY

=--TEXT(A1,"00\/00\/0000")

This will convert the value to a "real date" and you can then custom format as you wish.