Excel – How to make an Excel cell with a DATE format accept dashes

cell formatdatemicrosoft-excel-2013

I have an Excel sheet in which various date columns exist. Initially, all dates were in format "01/01/2000" but I wanted dashes, so I did a find-replace all expecting all the slashes (/) to be replaced by dashes (-).

This change occurred in some cells and not to all cells. All my cells have the same format as per below.

DateFormatAcrossAllCells

Now, I have various dates in my sheet such as 01/01/2000 and 01-01-2000.

I need to change all dates so they resemble 01-01-2000. I have tried to change the slashes to dashes manually but Excel switches the date back to what it initially was, with slashes.

However, I have filters applied on my date column and I have noticed that in the filter, some dates appear as a year with a collapsible menu which then shows a month with a collapsible menu which then shows a day. Other dates do not show this and show the date in full, such as, "01/01/2000". Sample below.

DateFilterMenu

The dates that are displayed with collapsible items, can accept dashes (-) if I type them in, the dates that are not displayed as collapsible items, like "01/01/2000" do not accept dashes. This is my problem.

So how can I make those dates accept dashes and how can I make all my dates become collapsible in the filter?

Happy to make any clarifications.

Best Answer

Had a bit of a fight with Excel but it turns out the steps to solving this craziness with the dates was for me to a bit more responsible before I started entering dates.

Solution

I wanted to have a date format for English (United Kingdom) but my Windows 10 format was set to English (US). So when I typed dd/mm/yyyy this was interpreted in Excel as mm/dd/yyyy. So I changed my settings.

Control Panel --> Clock and Region and on the Formats tab I chose the correct format, in this case, English (United Kingdom).

So I went back to my Excel sheet, removed all the dates from the date columns, changed the format for those columns to English (United Kingdom) and then re-added my dates.