DATEVALUE to force Excel entries into the Date format


Don't miss that you can use the DATEVALUE function in Excel to correctly format dates entered in a column, if selecting the cells and right clicking and selecting Format Cells and picking the date format on the Number tab doesn't do the trick.

In this example, a formula has been entered in column D to create a date based in numbers listed in the columns to the left. Even though column D is formatted for dates in the MM/DD/YYYY format, and the value of the formula has been pasted into the cells. The date can be correctly formatted by clicking into a cell, but we don't want to have to do this for the entire range.

Using DATEVALUE in a column to the right (just referencing the cells with the dates) will transform the dates to the correct format - or at least those dates that have not already been converted.