Skip to content

Excel Date Format Won’t Change | I Can’t Change Excel Date Format!

     

    In this video I explore a problem with dates and times, where Excel will not allow you to change the date/time format. This problem is caused by the dates and times being stored as text values. The solution is to use Text to Columns to convert the dates and times to numeric values. You will then be able to apply date and time formats in the normal way. 

    Click here to download the featured file.

    You can tell if a date is stored as a text value as it will appear left-aligned within a cell.  Dates, if entered correctly, are stored as numeric values which are by default aligned to the right within a cell.

    Why would your dates be stored as text values?  You may have imported dates in from another system which doesn’t store dates correctly for Excel or you may have just entered dates in the incorrect format by mistake.

    Converting Text Dates into Numeric Dates

    To convert text dates into numeric values, follow these steps:

    1. Start by selecting the dates in your worksheet
    2. Click on the Data tab on Excel’s ribbon
    3. Click on the Text to Columns button in the Data Tools group
    4. In the first step of the Convert Text to Columns Wizard, ensure Delimited is selected (this is the default) and then click Next
    5. In the second step, untick any delimiter options that are currently ticked and then click Next
    6. In the third step, select Date as your data format
    7. In the date drop-down select the format that your dates are currently in eg, DMY
    8. Click on Finish to complete the wizard

    Your dates should be now be right-aligned within their cells and you will be able to apply your chosen date format.  Watch the video above to see lots of examples of how you might use this technique.

    Converting Text Time & Date Values to Numeric Values

    If you have time and date values that you can’t change the format of, then the problem is likely to be the same: the values are stored as text rather than numbers.

    You can use exactly the same method as above to convert time and dates to numeric values.  However, step 3 of the wizard doesn’t allow you to specify the time and date format of your values, so you make a selection that applies to the date portion only.  The resulting values are not going to be what you expect though – some formatting will need to be applied. To apply the correct format, follow these steps:

    1. Select the cells that you want to apply the time and date format to
    2. Use CTRL 1 to open the Format Cells dialog
    3. On the Number tab make sure Custom is selected in the Category list
    4. In the Type: box replace any content with the following characters: dddd mmmm yyy hh:mm:ss
    5. Click on OK to confirm

    Separate Time and Date into Separate Columns

    If you want to separate the date and time into separate columns, do the following:

    1. Select the cells containing the date and time values
    2. Click on the Data tab on Excel’s ribbon
    3. Click on the Text to Columns button in the Data Tools group
    4. In the first step of the Convert Text to Columns Wizard, ensure Delimited is selected (this is the default) and then click Next
    5. In the second step, select the delimiter (character) that separates your dates and times, (for example a space)
    6. In the third step, in the Data Preview section, select the date column and specify Date as your data format
    7. In the date drop-down select the format that your dates are currently in eg, DMY
    8. Click on Finish to complete the wizard

    You will need to apply the time format to the time column in your worksheet otherwise the times will appear as normal numbers.  The time format is available from the format drop-down on the ribbon’s Home tab or via the Format Cells dialog (CTRL 1).