Tips & Tricks: Easily convert dates in Excel – 4 easy steps

By: Salomi Kruger

We are often asked if there is an easy way to convert dates in Excel, and yes there is. Here’s how: 

When you type in a date in Excel, it stores it as a number. It counts how many days that date is away from the 1st of January 1900 and assigns the appropriate number. For this reason, dates can be used in calculations in Excel and we can convert that number to the applicable day of the week, month, year etc. 

In the data below we have dates, but this can’t be analysed for weekday trends as we don’t have which day of the week each date represents.  

We will use the Text function to easily convert this and then we can use our data in a graph or Pivot for visualization. 

Step 1: Use the TEXT function by typing in “=TEXT(“ in the cell you would like to put your day of the week 

Step 2: Complete the function by choosing your date (in cell A2) as the “value” part of the function syntax.  

Step 3: Place your comma or inverted comma as a list separator of the Excel function (this depends on how your computer settings are set up) 

Step 4: Using D, M and Y and combinations thereof, format the number to become a day of the week by placing “DDDD” as the format text part of the function syntax and… voila… you have days of the week for your dates.

The End Result: