How to convert text to date format in Excel

Question

I need to sort this column chronologically, however when I import the data, some of the cells are recognized as a a custom format (A2-A4) while others are in a general format (A5-11). A2-A4 is recognized as a date, however A5-A11 is being recognized as general text. Excel is not recognizing that these should all be dates. I have tried formatting them as a date, but this does not work. I have used text to columns to separate the time from the data, however cells such as A2-A4 leave behind a time of midnight in the cell (e.g. 2/10/2012 0:00:00) and I still can't get the cells to appear as dates when I use text to columns. I have cleared formatting, I have tried format painter, I have tried =datevalue. I'm not sure what else to do. Also, I noticed that the cells where Excel recognizes the values as dates, the time is listed in 24hr time, where as the others are not.

Can someone please help?

text looks like this:

        Column A
2    2/10/2012 20:00
3    1/11/2012 3:00
4    5/12/2013 15:30
5    19/10/2012 2:00 pm
6    18/04/2013 2:00 pm
7    18/10/2013 2:30 pm
8    23/12/2013 9:00 am
9    31/05/2013 1:00 pm
10   16/02/2013 6:00 pm
11   15/02/2013 6:00 pm

Answer 1

I'll assume that, for some reason, adjusting the input data feed so that all the dates follow the convention assumed by Excel is impractical, because that's probably the easiest solution to your problem.

For the cells that are stored as text, I assume we know the format of the date that's being read (i.e. DD/MM/YYYY) and that it'll remain constant. For a string like DD/MM/YYYY HH:MM PM, we can then do the following:

(1) The day must be stored in the characters before the first slash, so we can extract it by writing:

DAY_FORMULA: =LEFT(A1, FIND("/", A1, 1)-1)

(2) The month must be stored in the characters between the first and the second slash. It occupies either one or two characters, so we can write the following:

MONTH_FORMULA: =MID(A1, FIND("/", A1, 1) + 1, x)

Here, FIND("/", A1, 1) + 1 tells Excel to return the location of the first /: since the month starts after that, we add one. Now we need to find x, the number of characters to read from that point. Since the first / is at FIND("/", A1, 1) and the second is at FIND("/", A1, FIND("/", A1, 1)+1), then the number of characters between the two slashes is given by:

FIND("/", A1, FIND("/", A1, 1)+1) - FIND("/", A1, 1) - 1.

The complete MONTH_FORMULA can be written as:

MONTH_FORMULA: =MID(A1, FIND("/", A1, 1)+1, FIND("/", A1, FIND("/", A1, 1)+1)-FIND("/", A1, 1)-1)

(3) The year is contained in the last four digits after the second slash, so we can write the year as:

YEAR_FORMULA: =MID(A1, FIND("/", A1, FIND("/", A1, 1)+1)+1, 4)

You can then use the standard date formula to force these into a date format:

=DATE(YEAR_FORMULA, MONTH_FORMULA, DAY_FORMULA

(4) If you want to keep information about the hours, then if no leading blanks exist in the fields (not that this couldn't be circumvented with a =TRIM), you can use:

TIME_FORMULA: =TIMEVALUE(MID(A1, FIND(" ", A1, 1)+1, LEN(A1)))

You can then add the date to the time value to obtain the full date-time:

=DATE(YEAR_FORMULA, MONTH_FORMULA, DAY_FORMULA) + TIME_FORMULA

You don't want to try to format a string into a date if the date is already properly formatted, so you could also add an =IF statement before performing any of the operations above:

=IF(ISTEXT(A1), ADJUST, A1)

The full solution, replacing ADJUST by the steps above, might look something like this:

=IF(ISTEXT(A1), DATE(MID(A1, FIND("/", A1, FIND("/", A1, 1)+1)+1, 4), MID(A1, FIND("/", A1, 1)+1, FIND("/", A1, FIND("/", A1, 1)+1)-FIND("/", A1, 1)-1), LEFT(A1, FIND("/", A1, 1)-1))+TIMEVALUE(MID(A1, FIND(" ", A1, 1)+1, LEN(A1))), A1)

Answer 2

As @Tim is hinting, the best way to address this may be in the source or in its importation but once in Excel it can still be dealt with by copying your text formatted dates and parsing these with Text to Columns and Fixed width with a break immediately after the year (at 10). In the process (Step 3 of 3) you can choose to treat the output for the left hand column as Date: MDY. Then add the date and time together and format to suit.

Answer 3

How do you import the data?

If you open the CSV file by double-clicking or via File > Open, then Excel will parse it and you don't get a chance to see the wizard. The date will be interpreted according to your regional settings. If the date format of your regional settings and the CSV file don't match, many dates will be wrong.

Instead, open a new file and go via Data > From Text. Navigate to the CSV file and open it. Then you will get the Import Wizard. In Step 3, select the column and set it to Date. Then select the order of day, month and year as it appears in the file.