Just came across this bug in Excel, Thought I should share with others for easy and quick solution.
Problem:
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.
Cause:
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.
Solution:
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.
VB:
Problem:
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.
Cause:
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.
Solution:
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.
VB:
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