We can calculate the desired number of characters (1 for months 1-9 or 2 for months 10-12) by subtracting 8 from the length of the entire string (using the LEN Excel function). Therefore, we need to make the second argument of the LEFT function dynamic. For dates where the month has two digits (for example, the text is “” = 13 October 2004), we in fact need the formula: =LEFT(A10,2) Unfortunately, this only works for months 1-9.
It means: get the first (one) character from the text in cell A10. Therefore, if we want to get the month from cell A10 (get the “1” from “”), the formula is: =LEFT(A10,1) It takes two arguments: the original text and the position of the last character to include. The LEFT function extracts the beginning of a string from the first character up to and including the n-th character. We must extract them as pieces of the text, using Excel functions LEFT and MID. We will do that using the DATE function again, but this time we must get the three arguments – year, month, day – differently. rows 10 and below in our example) are not recognized as dates in Excel, because the day (treated as month) is greater than 12 and there is of course no 13th month.įor instance, in cell A10 we have the text “” which we want to convert to the date 13 January 2004. … which returns the correct date: 2 January 2004. … so the values of our DATE arguments are: Select the column with dates and go to Excel main menu / Data / Text to Columns. Text to Columns Solutionįor those not familiar with the Text to Columns feature, let me first mention that one, because when it works, it is much faster than converting the dates using some formulas you build. This must be fixed before you can do any meaningful work with the data.
In sum, in the Date column you end up with two kinds of cells: Moreover, once you get to 13 January (row 10), Excel no longer treats the expressions as dates, because there is no 13th or 14th month. As a result, 2 January becomes 1 February, 5 January becomes 1 May and so on – the day and month have been reversed. My computer, which is looking for “d/m/yyyy” dates has no way to know that this particular CSV file stores dates in the “m/d/yyyy” format.
0 kommentar(er)
