CODEWITHSUNDEEP
exceldate
saurabh sharma
saurabh sharma

Reputation: 183

Excel date format mismatch

I have a huge excel file with one column containing the date. The problem is that the date in this column is in different formats as shown below:

12/5/17 
12/5/17 
15-05-17 0:00
19-05-17 0:00
19-05-17 0:00
22-05-17 0:00

Some have a time stamp in that, some are in dd/mm/yy or mm/dd/yy format. I need a single format mm/dd/yy form(no time stamp). I did try the basic methods, like formatting the cell (going to the category, choosing a single date format but for the values of m/d/yy h:mm;@ is not changing). I further tried using datevalue, to get the value in a new column by using the following formula: =DATEVALUE(MONTH(D2) & "/" & DAY(D2) & "/" & YEAR(D2)) Again, it is not working. Any ideas to fix this shall be highly appreciated? Thanks

Upvotes: 0

Views: 1683

Answers (1)

teylyn
teylyn

Reputation: 35990

Well, it looks very much as if the "dates" are text, because they show in so many different formats.

Select the dates, click Data > Text to columns > Next > Next. In Step 3 of the wizard, mark the column as a Date column and from the dropdown select the order that the source data is in, i.e. DMY. Then hit Finish and the dates will show as dates using the format of your regional settings.

enter image description here

Now you can strip the time by using a helper column with the INT() function, or just format the values to show without the time.

Upvotes: 1

Related Questions