Fixing Dates

Dates are one of the most common areas of inconsistency. Date formats are an issue, but people also like to include 2 dates in a cell e.g. a from and to date in the same cell.

Two dates in one cell

To follow along go to 3.6

Ideally get each date into its own cell and make sure it is recognised by Excel as a date.

Fixing Dates

Excel not recognising the date

Occasionally you get a situation where you receive information that to the human eye is clearly a date but Excel doesn’t recognize it e.g. US format dates on a South African machine.

To follow along go to 3.5

In this case, you can use the CONVERT TEXT to COLUMNs tool to tell excel to read the cells as dates. The CONVERT TEXT TO COLUMNS tool is found in the DATA tab in DATA TOOLS box as shown below.

Fixing Dates

Once you have highlighted the cells concerned and clicked the TEXT to COLUMNS button the standard dialogue box will appear. In this case as we have only one column we can ignore any delimination requirements and go straight to the format section. Here you can identify the cells as a date (see below) and then tell Excel what the current date format is. In this case it is showing MONTH, DAY, YEAR so you would choose MDY.

Fixing Dates

Back to: Excel Dashboards and Management Reporting > Getting the data right for a dashboard