Is there a formula to fix this issue

Copper Contributor

I actually wrote to my bank's Pres. about this: When I download any report from my local bank, the first thing I want to do is sort it by the DATE field. However, their field for DATE is formatted as GENERAL (no specific number format (you'd think a bank would know better)!!! Therefore, 1/27/2023 sorts lower than 1/3/2023 - or - 12/1/2023 sorts lower than 2/1/2023. Is there a formula I can use to pad ZEROS or to change the syntax from 9/9/9999 to 99/99/9999? I imagine the first thing I need to do is to change it to numeric(?) but I'm not successful in doing that.

3 Replies


Select the Date column.

On the Data tab of the ribbon, click Text to Columns.

Select Delimited, then click 'Next >'.

Clear all check boxes for the delimiters (Tab, Comma, etc.), then click 'Next >.

Select MDY from the Date drop-down, then click Finish.

Does that help?

Thank you SO MUCH! This has been a thorn in my side for years. So grateful for your invaluable help and expertise. What is this process called? In COBOL, we called it "Reference Modification."


I don't think there is a specific name for it in Excel...