Advanced Excel Date Editing and Formatting

Suppose you have an Excel file with dates in the format DD/MM/YYYY.

So you have entries like 02/03/1999 (2 March 1999). Suppose you want to change it to 2/3/1999, i.e. remove leading zeroes from one digit days and months.

It sounds easy, but it is actually quite tricky to do it in Excel without knowledge of the correct method. The normal methods of date formatting will simply not work.

After some searching, I found and tested that the following method is probably the fastest and easiest way to do it:

Select the date column and then click Data > Text to Columns Next, Next, then select Date ‘MDY’

then you should be able to do this using a number format of m/dd/yyyy or m/d/yyyy if you don’t want a leading zero in the days as well

Source: StackOverflow

Author: mathtuition88

Math and Education Blog View all posts by mathtuition88