<div dir="ltr" style="text-align: left;"><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Just came across this bug in Excel, Thought I should share with others for easy and quick solution.</span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><b>Problem: ;</b></span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Can&#8217;t change the format of the date column. It doesn&#8217;t seem to be responding to any date format changes I try to make.</span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><b>Cause: ;</b></span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">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.</span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><b>Solution: ;</b></span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">1.Work around is to fill the blank cell into number , Delete required cell value , Paste blank cell value (number) and re- enter date ( <span style="color: red;">unpredictable</span>) </span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">2. Select Cell (date) Click Data >; Text to Columns, click Next twice, tick Date and select MDY then click Finish. ; (<span style="color: red;">Worked</span>)</span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">3. Run Following Code ( you need to enable macro option to run this code) and apply to all worksheet. </span></span><br /><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">VB:</span></span></p>
<div>
<pre style="height: 144px;"><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span>Option Explicit</span> <br /> <br /><span>Sub</span> PrepareData() <br /> <span>Dim</span> F <span>As</span> Range <br /> Application.ScreenUpdating = <span>False</span> <br /> <span>For Each</span> F <span>In</span> ActiveSheet.UsedRange <br /> F.Value = F.Value <br /> <span>Next</span> F <br /> Application.ScreenUpdating = <span>True</span> <br /><span>End Sub</span> ;</span></span></pre>
<pre style="height: 144px;"><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"> ;</span></span></pre>
<pre style="height: 144px;"> ;</pre>
<pre style="height: 144px;"> ;</pre>
</div>
</div>

The Cyber Security, Compliance & Cloud
Cyber Security | Digital Transformation | Cloud Computing
