Changing YYYYMMDD to MM/DD/YYYY. Changing the date format to yyyy-mm-dd. Excel Date Conversion from yyyymmdd to mm/dd/yyyy. Excel seems to change format of only some columns. VBA: Change Excel cell width. Nested function in excel formula. Dd/mm/yyyy and hh:mm:ss to yyyymmddhhmmss. Convert Date to Text using Text Function TEXT function is best used when you want to display a value in a specific format. In this case, it would be to display the date (which is a number) in the date format.
Here I will talk about using DateValue function to change the text dates to proper Excel dates. When you input '2013/03/21 into a cell, that apostrophe before the date is used to indicate to Excel that the value in the cell is text value (see following screenshot). You can convert this text dates to Excel date as following steps: 1. In the adjacent cell B1, enter the formula = Datevalue(A1), see screenshot: 2. Then press Enter key, the formula returns a number 41317.
This is the correct answer, but it has the wrong format. So you need to change the date format. Select the cell B1 and right-click, choose Format Cells from the context menu. In the Format Cells dialog box, click Number > Date, and choose the local date as you need. See screenshot: 4.
Then click OK, the number has been converted to normal Excel date. And then select the cell B1 and drag the fill handle over to the range that you want to contain this formula. See screenshot: Tip: Maybe you also have this date format 2013-mar-12, the Datevalue function can change it to Excel date as well. Do as this: 1. Enter the formula =DATEVALUE(RIGHT(A1,2)&'-'&MID(A1,6,3)&'-'&LEFT(A1,4)) into the blank cell B1. See screenshot: 2. Then tap the Enter key, select cell B1, and then drag the fill handle to the range you need.