EXCEL - Date Formatting

exp vg
exp vg used Ask the Experts™
on
I have a spreadsheet, where one column has dates formatted in two different ways

Examples

5/16/77

OR

08/22/78

For the cells where the month has  leading zero, the dates are not grouped by year in the filter drop down. If there is no leading zero, in the filter drop down they are grouped by year.

I have tried to reformat the entire column, but this does not change.

Additionally, when I export this spreadsheet/table into excel, the dates that have the leading zero do not import and these cells are blank.

Please advise on how to reformat.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Professor JMicrosoft Excel Expert
Top Expert 2014
Commented:
you can use a helper column with function of TEXT to keep the leading zeros

for example  =text(a1,"mm/dd/yy")
MlandaT
Commented:
If cells are showing a leading zero on '08/22/78' and since we know it's not due to a Date Format, then it's probably because the values in these cells are strings which have been captured with an apostrophe character in front. See if this is the case and remove those leading apostrophes from the cell values.
exp vg

Author

Commented:
I do not see a zero in front of the cells with a leading zero.

Thanks.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Learn More!
Microsoft Excel Expert
Top Expert 2014
Commented:
@Exp Vg

if my formula above does not give you correct result this use this one

this example is used where you date is in A2

=TEXT(DATEVALUE(A2),"mm/dd/yy")
exp vg

Author

Commented:
Thank you
MlandaT

Commented:
@ProfessorJimJam's first solution seems to work. Tried it and it fixed the case where I had a leading '
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
better yet use this one, it will take care of everything


=IFERROR(TEXT(DATEVALUE(A2),"mm/dd/yy"),TEXT(A2,"mm/dd/yy"))+0
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
please see it in the attached workbook
Book1.xlsx
exp vg

Author

Commented:
Super thank both of you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial