Excel Date Formatting

Question

I have a database download from my Customer's site with a column titled "Date Opened" formatted as YYYY-MM-DDT00:00:00. The time reference is the same in all rows, only the date changes.  I need to eliminate the T00:00:00 from all rows leaving only the date with a different format, MM/DD/YYYY. I am unable to change the format of these cells through any means I have tried including several from other discussions. Does anyone know how I can accomplish this?

Answer 1

Hi,

Select the range and press Ctrl+H. In the Find What box, type T* and click on Replace All.

Hope this helps.

Answer 2

Use Excel’s date conversion and custom formatting so the underlying value stays a date and only the display changes.

1. First, convert the text-like values to real dates (if needed):
   • Select the “Date Opened” column.
   • On the Home tab, in the Number group, open the Number Format dropdown.
   • Choose Short Date or Long Date. This converts recognized date/time strings into date values.
2. Then apply the desired display format (MM/DD/YYYY):
   • With the same column still selected, press Ctrl+1 to open Format Cells.
   • Go to the Number tab.
   • Select Date and pick a close format, or go directly to Custom.
   • In Category, choose Custom.
   • In Type, enter:

          mm/dd/yyyy
     

   • Select OK.

The time portion (T00:00:00) will no longer display; only the date in MM/DD/YYYY format will show. The underlying value remains a full datetime, so it can still be used in date calculations and sorting.

If the column still shows the literal YYYY-MM-DDT00:00:00 text after these steps, Excel is treating it as text. In that case, use Power Query to convert the data type:

1. Load the data into Power Query (Data > Get Data / From Table/Range).
2. In Power Query, select the Date Opened column.
3. Select Home > Transform > Data Type > Date.
4. Select Home > Close & Load to return the transformed data to Excel.
5. Then apply the mm/dd/yyyy custom format as above.

---

References:

• Format a date the way you want in Excel
• Format numbers as dates or times
• Convert dates stored as text to dates
• Convert a data type
• Converting date format - Microsoft Q&A