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
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
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.
=TODAY()
The NOW function returns the current date & time.
=NOW()
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.
=TODAY()-1
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.
Here is a post & video tutorial on how dates work and the calendar system in Excel.
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.
Here are posts & videos on how to convert text to dates or times with Find and Replace, Text to Columns, Flash Fill, Formulas, and Power Query.
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! 🙂
How can i sort dates so that regardless of the year, the month/day sorts together?
I would add a helper column with the formula “=DATE(2020,MONTH(A1),DAY(A1))” – where A1 is the cell ref of the first date – copy down and sort by that
Thank you Jim! I will give it a try. Appreciate the quick response.
How to input year in any type of filling form?
Great tip Jim! Thanks for sharing!
Danger Will Robinson: Note that 1/1/1900 = Day 1 but assumes 1900 is a leap year. So 1/1/1901 = Day 367. Since 1900 is not a leap year, Steve Jobs had the counting on Excel for the Mac start at 1/1/1904 which was a leap year. If you enter dates on a Mac, then move the file to a PC, the dates will be wrong. Use the Options | Advanced | Use 1904 date system option to turn off the 1904 dates on the Mac so the dates start at 1/1/1900, save the file, then open it on a PC. Turn the 1904 date system back on on the Mac if you like.
Thanks for the suggestion, Don! 🙂
Mind…blown. Thank you for this!
I subscribed to Excel Campus, and it was the best purchase I made all year. Jon, the quality of your training videos and the rapid response time to questions has set the bar high for all other would be trainers/educators. Thank you for the great content.
Hey Cameron,
Thank you for the nice comment. I’m happy to hear you are enjoying our training programs and appreciate the great feedback.
Thanks again and have a nice day! 🙂
It never occurred to me to use the format drop-down in this way. So obvious in hindsight. Thank you, nice one!
Interesting, but only works for one cell at a time
To check multiple cells, highlight the suspected date cells and look at the summary in the status bar; if the Numerical Count equals the Count, then they’re dates (NB you may need to enable these counts by right-clicking the status bar and selecting)
To convert text-that-looks-like-dates into actual dates, copy an empty cell, select your target cells and paste special, checking the “Values” and “Add” options
Cool
Great tips! Thanks again, Jim! 🙂
Once again, I marvel at how helpful your tips are. Even though I’ve used Excel for years, I still consider myself a newbie, so thank you so much.
Thank you, Tim! I really appreciate your support and the nice feedback.
This is a great reminder for all Excel users (myself included) to approach Excel with a newbie/curious mindset. There are so many things to learn and infinite possibilities with this tool. Sometimes the biggest challenge can be opening our minds to new techniques and ways of doing things. So, I really like your approach.
Thanks again and have a good one! 🙂
I work with dates and times a lot. Here’s is something I found essential (but rare): In Windows Excel 365 getting “negative time”, e.g., “=12/31/2020-1/1/2021” is doable by selecting “1904 Date Option” in Options… Advanced. Of course, this works with whole days as well as hours and minutes. Play with formatting.
Be careful with this workbook setting because it may change all existing date calculations– do it before you build worksheet.
Interesting. Thanks for sharing, Kim! 🙂
I am learning excel knowledge and computer knowledge only
When you add hours in Excel, the total starts over at 24 hours.
To add hours in Excel, you need to format the cell as [hh]:mm. This will let you add hours beyond 24, which is useful for weekly timesheets or projects.
Great tip! Thanks for sharing, Stephen! 🙂
Sometimes, when I import data from a CSV, the data is automatically converted as a date, although it’s not. The text can be something like SEP-20 (which is actually a name of a protein).
How can I make sure this is not converted as a date?
Thanks Jon et al.
When importing via the wizard, you should be able to specify that column is to be treated as text
If you’re copying and pasting from a csv, you need to format the receiving column as text, then copy and paste the csv data AS VALUES (this really should be the default)
If you still have this problem, it would be useful to know the steps you take for this process
jim
Hello, for some reason these shortcuts (Ctr+; and Ctrl+Shift+;) don’t work in my Excel. Any ideas what could be the reason for this?
I tried different languages as well but nothing helped.
Could it be that you are using the plus sign? You can just hold down the Ctrl key and then tap the semicolon key.
If you paste data into Excel that includes the day of the week spelled out (“Tuesday, December 29 2020”) There’s nothing you can do to make Excel recognize it as a date — until you strip out the day. Took me a long time to learn this, because Excel even has a date format that looks exactly like that. So I use a simple macro to strip out days of the week from the date columns in documents.
or you could use text-to-columns (on the Data tab of the Ribbon), specifying comma as the separator
Related to dates, I just learned about the difference between WEEKNUM and ISOWEEKNUM. This year can cause a discrepancy depending on what you count as week one. WEEKNUM makes Jan 1 week 1 regardless of the day. If you prefer to follow ISO standards (weeks begin on Monday & ‘week 1’ is assigned to the first week containing a Thursday), you can use ISOWEEKNUM (or WEEKNUM with return_type=21). Was so I glad I figured this out before I rigged a workaround!
Hi Jon! Great sharing as always!
I may include the DATEDIF formula also when we talk about dates. It is a great tool to calculate the difference of days, months, and years between two dates in excel.