Excel date format mismatch

0 votes

I have a sizable excel file with the date listed in one column. The issue is that the date in this column is shown in various ways, as follows:

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 of them include a time stamp, while others use the format dd/mm/yy or mm/dd/yy. I require a single mm/dd/yy form (no time stamp). I have tried the fundamental techniques, such as formatting the cell by selecting the category and selecting a single date format, but the values of m/d/yy h:mm;@ remain unchanged. I also tried using datevalue with the following formula to retrieve the value in a new column: DAY(D2) & "/" & YEAR(D2) & "/" & MONTH(D2) & "/" Once more, it is ineffective. Any solutions to resolve this would be greatly appreciated. 

Oct 30 in Others by Kithuzzz
• 20,660 points
61 views

1 answer to this question.

0 votes

Due to the fact that the "dates" appear in so many distinct formats, it appears as though they are text in most cases.

Click Data > Text to columns > Next > Next after selecting the dates. Mark the column as a date column in Step 3 of the process and choose DMY from the option to reflect the order of the source data. Once you click Finish, the dates will appear using the format that is set for your region.

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.

answered Oct 30 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
531 views
0 votes
1 answer

Convert a date format in PHP

Use strtotime() and date(): $originalDate = "2010-03-21"; $newDate ...READ MORE

answered Feb 17 in Others by Aditya
• 7,660 points
1,249 views
0 votes
1 answer

Is there any way to change input type="date" format?

To answer your question, it is impossible ...READ MORE

answered Feb 18 in Others by Aditya
• 7,660 points
8,598 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
405 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
142 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
242 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
71 views
0 votes
1 answer
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
215 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP