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, 2022 in Others by Kithuzzz
• 38,000 points
1,836 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, 2022 by narikkadan
• 63,600 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, 2022 in Others by gaurav
• 23,260 points
1,633 views
0 votes
1 answer

How to subtract date only format "mm/dd" in excel?

I suppose Excel has handled December 21 ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,600 points
794 views
0 votes
1 answer

Date format conversion in Excel

You appear to be misunderstanding the point: ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,600 points
503 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, 2022 in Others by Edureka
• 13,690 points
4,066 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, 2022 in Others by Edureka
• 13,690 points
2,001 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, 2022 in Others by Edureka
• 13,690 points
981 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,704 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
3,551 views
0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,600 points
1,437 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