Log In
Register
Home
New Member
August 3, 2019
Offline
I tried all the techniques in this document:
https://www.myonlinetraininghu.....t-in-excel
Still wont fix my bad dates. Can someone help me please. I attached the spreadsheet
Thanks
Michael
VIP
Trusted Members
June 25, 2016
Offline
Hi Michael
Just multiply each date with 1, for example C2*1 etc.
Hope this helps
Sunny
VIP
April 21, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
Power Query
Power Pivot
Power BI
October 17, 2018
Offline
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
1 Guest(s)
