asked on
EXCEL - Date Formatting
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.
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.
Microsoft Excel
Last Comment
exp vg
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you
@ProfessorJimJam's first solution seems to work. Tried it and it fixed the case where I had a leading '
better yet use this one, it will take care of everything
=IFERROR(TEXT(DATEVALUE(A2
=IFERROR(TEXT(DATEVALUE(A2
please see it in the attached workbook
Book1.xlsx
Book1.xlsx
ASKER
Super thank both of you.
Thanks.