Working with Dates in Microsoft Excel

Jun 2, 2022 | Excel Hints and Tips

Dates behave quite differently to other data when using some of the features in Microsoft Excel.  It’s helpful to understand what options are available to ensure you work with them as effectively as possible. 

Entering dates

It is vital that dates are entered correctly so that Excel understands that they are dates and not just normal text.  Most ways that dates are likely to be entered are perfectly OK but entering it as dd.mm.yyyy format or similar does not work.  If dates are entered using dots then Excel thinks it is text.  If the alignment in the cells hasn’t been changed it is easy to pick up on this as text is aligned by default to the left of the cell and dates to the right. 

Luckily there is a quick way to solve this issue if it does occur by replacing the full stop with a forward slash thus turning it into a date format that Excel understands. 

  1. Select the column or range of cells containing the dates in dd.mm.yyyy format 
  2. From the Home tab select Replace
  3. In Find What type ‘.’ 
  4. In Replace With type ‘/’ 
Replace dates

Filtering with Dates

Filtering with dates brings up distinctly different options than filtering with other data types. 

  1. On the Data Tab select Filter 
  2. Click on the drop down in the header row next to a column containing dates 
  3. Where you would normally see a list of options to choose from these are divided into years, months and then specific dates.  This is really useful as often the data you require will be for a specific month or year and it makes it much easier than entering a specific range of dates of ticking multiple boxes to select all the dates in that period. 
Filter date

4. There is also an option of Date Filters which gives choices such as this week, last week next week and this month, last month, next month which can also be useful. 

date filters

Dates in Pivot tables 

Dates can be added to the rows, column or filters part of the pivot table pane or used with timelines as an alternative way to filter the data. 

Adding dates to rows or columns 

When a date is added to a row or column all the dates in a month are grouped and each month can be expanded to show the individual dates in that time period.  The Date field also now had a month field too.  This can be removed from the rows or columns if it is not required. 

Row and columns pivot table

Adding a Timeline 

Timelines are used to filter dates effectively.  When a date is added to a timeline it defaults to grouping the data by month so only whole months can be selected.  The drop down arrow that appears at the top right of the timeline box enables the dates to be grouped by years or quarters or if every date needs to be viewed then Days can be chosen instead. 

Timeline

NOTE: This is easier to use than the pivot table filter where each individual date is shown.  Filters make filtering longer periods of time fiddly and time consuming.

Further Reading

If you’ve enjoyed reading about different ways to effectively work with dates in Microsoft Excel, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.