3

The dates field in my dataset is of 2 different formats. Even though I formatted the cells to be in dd/mm/yyyy for all the cells, is it still the same.

The issue is because these dates are from different datasets. I merged them under one column. Any solution for this?

attached image for reference.different date formats

vishnu prashanth
  • 409
  • 11
  • 21
  • Can I assume that your "dates" are formatted as "text"? – K.Dᴀᴠɪs Feb 10 '18 at 15:53
  • You will probably have to format them correctly as you pull them from each data set if they are formatted as strings. Otherwise there will be no way to determine on the fly if it is mm/dd or dd/mm for certain dates – Joe Feb 10 '18 at 15:55
  • For now, the dates are in dd/mm/yyyy format. If I change the format to text, for example, 09/04/2016 changes to 42469 and 28/08/2016 is shown as a date. – vishnu prashanth Feb 10 '18 at 15:56
  • When I am changing the formats from the two files separately and when merging together, the format is same. when I merge it and then change, it doesn't reflect. Since there are a lot of files to merge, it is time-consuming to change the date format individually and then merging it. That's why wanted to check if there is any other easy way to do this.@Joe – vishnu prashanth Feb 10 '18 at 16:04
  • See [this](https://stackoverflow.com/questions/20375233/excel-vba-convert-text-to-date/30609676#30609676) and heed the warning. –  Feb 10 '18 at 18:25
  • Your problem is likely that the date format of the data source is `DMY` and your Windows Regional Short Date format is `MDY`. The result is that the dates where `D>12` are imported as text strings, and the other dates are **INcorrectly** being interpreted as `MDY`. Easiest solution is to use the Data Import wizard to properly identify the incoming date format. Another option is to change your Windows Regional Short Date format **BEFORE** doing the import. – Ron Rosenfeld Feb 11 '18 at 17:51

1 Answers1

0

This can be done with an Excel formula. If you have more than two date formats to deal with, add one "type" code for each format and expand the formula discussed below

Assumptions:

I assume you read the dates from various datasets, write them to a text file (CSV file), then open the CSV file with Excel. I assume all the dates in any one dataset are in the same format, all either dd/mm/yyyy or mm/dd/yyyy. When you write the dates from one dataset to a CSV file, I assume you can format them so that the day and month values have leading zeroes so that all dates are always 10 characters wide. I assume that when you write the dates from one dataset to a CSV file, you can include additional text identifying the date format (see recommendations)

Recommendations:

Important: when you write a date to a csv file, include an apostrophe as the first character. This ensures that Excel interprets them as Excel text and not Excel dates. The formula I provide will cast your text data as dates

  1. When you read a dataset that has dates in the format dd/mm/yyyy, write each date to a line in the CSV file like this:

TYPE1,'dd/mm/yyyy

  1. When you read a dataset that has dates in the format mm/dd/yyyy, write each date to a line in the CSV file like this:

TYPE2,'mm/dd/yyyy

  1. After you've created all the CSV files, concatenate them into 1 CSV file

  2. Open the CSV file from step 3 with Excel. Column A will have either TYPE1 or TYPE2. Column B will have all the dates (in text format because of the apostrophe you added).

  3. On the first row that has a date (assuming row 2 for this example), in column C, enter this formula:

=IF(A2="TYPE1",DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)),DATE(RIGHT(B2,4),LEFT(B2,2),MID(B2,4,2)))

copy and paste this cell downward for all rows. Column C will then show all dates in the format mm/dd/yyyy

See screen shot

excel sheet with formula

  • Thank you so much for the answer. But both the date formats are in dd/mm/yyyy only. I don't know what is making them look different. – vishnu prashanth Feb 10 '18 at 18:31
  • We shouldn't/cant discuss on this forum. If you'd like to discuss further, use my email william.s.charlton@outlook.com. I think the problem is how the dates are being written to the file that you're viewing using Excel. Please give me the exact details of what you're doing via my email –  Feb 11 '18 at 00:52