Quick Tips for Dates in Excel

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).

Excel Keyboard Shortcut to Input Current Date 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+;

Excel Keyboard Shortcut to Input Current Date and Time in Cell

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.

Format Cells window for Date and Time Format in Excel

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.

TODAY Function returns current date in Excel

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.

January 1 1900 Start of Excel Calendar One Number Equals One Day

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.

Not a Date Previews on Format Drop-down in Excel

If the format previews are different and the Number format shows a whole number then this is a date value.

Date Previews on Format Drop-down in Excel to determin if value is a date

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! 🙂

  • 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.

  • 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! 🙂

    • 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

  • 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.

  • 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.

  • 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.

  • 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!

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >