Impossible to change the date format in Excel ” Date Format Bug “

Sharing is caring!

Just came across this bug in Excel, Thought I should share with others for easy and quick solution.


Can’t change the format of the date column. It doesn’t seem to be responding to any date format changes I try to make.

It seems excel is keeping the format of the cell into text and not changing it to date format. In most cases this happens when data is imported to excel.

1.Work around is to fill the blank cell into number , Delete required cell value , Paste blank cell value (number) and re- enter date ( unpredictable)

2. Select Cell (date) Click Data > Text to Columns, click Next twice, tick Date and select MDY then click Finish.  (Worked)

3. Run Following Code ( you need to enable macro option to run this code) and apply to all worksheet.
Option Explicit 

Sub PrepareData()
Dim F As Range
Application.ScreenUpdating = False
For Each F In ActiveSheet.UsedRange
F.Value = F.Value
Next F
Application.ScreenUpdating = True
End Sub 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.