Changing date format without adding extra columns

Question

I have a large spreadsheet being compiled and sorted using macros. However, the date format is incorrect (see attached). I need the dates to display as 10/09/2024 or 10-Sep-24. How can I change the format using a formula or VBA code without adding an extra column?  ![](https://learn-attachment.microsoft.com/api/attachments/a33ad5d3-0664-4d23-a80c-e9f261f8b597?platform=QnA

Answer 1

Here is a procedure to re-format the dates that you want to change.

1. Select all of the dates that need to be changed.
2. On the Data ribbon in the Data Tools section, select Text to Columns.
3. In the Convert Text window leave the first screen on Delimited and click Next, and on the second screen leave Tab selected and click Next.
4. On the third screen choose Date and, in the dropdown, choose DMY. Then click Finish.

That will re-enter the dates as number values and formatted the way you want.

Answer 2

Thank you very much Rich~M. You helped my a lot. Very much appreciated