Bottom line: Learn some tips and keyboard shortcuts for working with dates in Excel.
Skill level: Beginner
The Importance of Dates
With year-end approaching, you might be working on planning, budgeting, calendars, reporting, etc. These type of data tasks typically require the use of dates.
Dates can be a challenge to work with in Excel. Especially when it comes to the date data type and making sure Excel recognizes your data as a date instead of text.
So this post covers a few quick tips for inserting and working with dates in Excel. There are links throughout the post to articles with additional details.
1. Inserting Dates in Excel
When inserting dates in cells, we can either input static dates that do not change, or use formulas for dynamic dates that calculate the current date or time period.
Static dates are date values in a cell that do NOT update.
The keyboard shortcut to insert the current date in Excel is: Ctrl+; (semicolon).
After pressing the shortcut, the date will be input and the active cell will be in edit mode. Press Enter to confirm the change.
Pro tip: To input the current date in multiple cells: first select the range, then press Ctrl+; , then press Ctrl+Enter to input the value in all of the selected cells.
The shortcut for the current time is Ctrl+Shift+; (semicolon).
To input the current date and time: press Ctrl+; then Space, then Ctrl+Shift+;
It's important to note that you will need to change the number formatting for the date to one that includes both the date & time.
It's also important to note that any existing data or formula in the cell will be replaced with the date or time when you press the shortcut. However, you can press Escape to restore the data.
To learn how to press the shortcuts and learn more of them, checkout our list of over 270 Excel keyboard shortcuts for Windows & Mac.
Dynamic dates are dates that update to the current date or time every time the workbook is calculated. Functions can be used in Excel formulas in input dynamic dates.
The TODAY function returns today's date. Input the following formula in a cell to return the current date.
The NOW function returns the current date & time.
These functions are volatile, meaning they will recalculate every time a change is made in the workbook or it is calculated. So it's best to use these if you want the cell to always return the current date and time.
Dynamic dates can be good for models where you want to apply filter criteria to a report for the current time period (day, week, month, year, etc.) every time the user opens the workbook. Here's an example of filtering a pivot table for the most recent period.
2. Calculating Dates
One day in Excel is equivalent to one whole number. This means if we want to calculate a date in the future or past, we can add or subtract whole numbers (days) to it.
The following formula will return yesterday's date.
Another way to see this is to enter a 1 in a cell and then change the formatting to a Date format. The date will be January 1, 1900. This is the day the calendar starts in Excel. Now change the value to 2. The date is January 2, 1900.
Therefore, today's date of December 23, 2020 equals 44,188. This is the number of days that have elapsed since January 1, 1900.
The Date & Time menu on the Formulas tab of the Ribbon is another great place to find all the date functions that can be used to calculate dates and time periods in formulas.
3. Date Data Types
If it looks like a date, and smells like a date, then…
it still might NOT be a date.
If you are working with data from CSV files or exported from online systems and databases, then Excel does not always interpret the various date formats as actual dates.
An easy way to tell if the value is a date data type is to select a cell that contains a “date” and look at the Format drop-down on the Home tab of the ribbon. If all of the format previews are the same, then Excel thinks the value is Text and NOT a date. Excel is storing the value as text.
If the format previews are different and the Number format shows a whole number then this is a date value.
You'll typically want to convert this text to a date value because of all the analytics features for dates in Excel. This includes date grouping for filters & pivot tables, date functions & formulas, number formatting, conditional formatting, etc.
Other Tips for Dates?
Those are some basic tips for entering and working with dates in Excel. Excel's ability to calculate and perform analytics with dates is extremely powerful, and helps simplify report creation.
Please leave a comment below with any additional tips you have for working with dates. We'd love to learn from everyone.
I hope you have a Merry Christmas & Happy Holidays! 🙂