@hossam sadek , when I opened your file attached to msg#1 I noticed in column B after row 135 that the values were proper Excel dates. If you look at these dates (especially if you format the cells to show the month
names instead of month
numbers) you'll see that they're not in January as you expect. Those dates have all been misinterpreted by Excel when those values entered the cells.
Looking at the cells above row 135, which Excel hasn't interpreted at all, I can see that the dates are in the month/day/year format.
Your first job, before you try to remove the time element, is to make sure that these rows below row 135 are NOT misinterpreted.
So…
1. how are you getting those data into the sheet?
2. If it's from a file (txt? csv?) then attach such a file too.
3. What version of Excel are you using?
ps. There is a horrible formula you can place in cell D2 and copy down which will extract just the date part and correct misinterpreted excel dates and handle the plain text dates that Excel hasn't interpreted at all:
Code:
=IF(ISNUMBER(B2),DATE(YEAR(B2),DAY(B2),MONTH(B2)),DATE(2000+MID(B2,FIND("/",B2,FIND("/",B2)+1)+1,2),LEFT(FIND("/",B2)-1),MID(B2,FIND("/",B2)+1,FIND("/",B2,FIND("/",B2)+1)-FIND("/",B2)-1)))
edit post posting: I've just realised the file you attached
is a csv file, so you can ignore all the above
.
I will attach a Power Query solution in a short while…
See attached. Right-click the table and choose Refresh.
BUT, because your csv file is unlikely to be in the same place as mine it won't work, so I've placed a picture in the attached of the steps to take to put that right.
If the file is always named the same and in the same location then it will be a case of just refreshing. If not then I can add a little routine to allow you to pick the file (hope you have Excel 2016 or later)…