Hi Michael

Just multiply each date with 1, for example C2*1 etc.

Hope this helps

Sunny

I tried this in the sample and it works. However when you copy that new data from column say I to column C and paste it as values, the dates in the cells which now also are behaving 'strange' won't be recognized as dates at first. You have to paste the cell layout from a 'good' cell to these and then everything is fine.

My question is how come this works? And all other efforts don't? It seems like Excel has a kind of memory that's blocking some of these changes?

I like the solution and it is one of the best I found for these kind of problems. But why does Excel it this way?

Frans

Hi Frans

Those cells you mentioned (with the "stubborn"dates) are formatted as General and not Date so they show numbers instead of dates.

When you copy and paste as values, it doesn't override the format of the target cells.

You will need to format them to Date.

Sunny

That is a nice method.  I always learn a lot just by reading the forum.  Everyone is so kind and generous with their knowledge.  I usually do a "text to columns" on the whole range of dates including the sticky dates, changing the column format to dates.  In this example I select the DMY in the drop down, as mine defaults to MDY.

Steve