The three ways to enter a date in Excel

Excel recognizes dates when you type them in a format it understands. The simplest method is to type the date directly into a cell using a common format: 1/15/2024, 01/15/2024, January 15, 2024, or 1-15-2024. Excel will automatically format it as a date and let you sort and calculate with it.

If you want today's date without typing it manually, type =TODAY() into any cell and press Enter. Excel fills in the current date and updates it automatically each time you open the file. For the current date and time together, use =NOW() instead.

The third method is to use the date picker. Click on a cell, then look for the small calendar icon in the ribbon at the top (this appears in some versions of Excel). Click it, select your date from the calendar that pops up, and Excel enters it for you. Not all versions have this feature visible by default.

Key Takeaways

  • Type dates in a standard format like 1/15/2024 or January 15, 2024, and Excel recognizes them automatically.
  • Use =TODAY() to insert today's date, which updates each time you open the file.
  • Once a date is entered, you can sort columns by date, calculate the number of days between two dates, and format how the date appears.
  • Excel stores dates as numbers behind the scenes, which is why you can do math with them.

Formatting dates so they display the way you want

After you enter a date, Excel may display it differently than you typed it. To change how it looks, right-click the cell (or cells) containing the date and select Format Cells. A window opens with a Number tab at the top.

Click the Category list on the left and select Date. You will see a list of date formats on the right: 1/15/2024, Tuesday, January 15, 2024, 15-Jan, and many others. Click the format you want and click OK. The dates in your selected cells will display in that format without changing the actual data underneath.

If none of the built-in formats match what you need, you can create a custom format. In the same Format Cells window, look for a Custom category at the bottom of the list. This is for advanced users, but common custom codes include MMMM DD, YYYY (for "January 15, 2024") and DD/MM/YYYY (for international date order).

Calculating the number of days between two dates

Once dates are in your spreadsheet, you can find how many days apart they are. In an empty cell, type = and then click the cell with the later date, type -, and click the cell with the earlier date. Press Enter. Excel shows the number of days between them.

For example, if cell A1 contains 1/1/2024 and cell B1 contains 1/15/2024, typing =B1-A1 in cell C1 will show 14. This works because Excel stores dates as numbers (January 1, 1900 is day 1, January 2, 1900 is day 2, and so on), so subtraction gives you the difference in days.

If you want to add days to a date instead, use the same method in reverse. Type =A1+30 to find the date 30 days after the date in A1. This is useful for calculating due dates or important date.

Sorting and filtering by date

When your dates are properly formatted, you can sort a spreadsheet by date order. Click any cell in the column with dates, then go to the Data menu at the top and select Sort A to Z (oldest to newest) or Sort Z to A (newest to oldest). Excel rearranges all rows so they line up with the new date order.

You can also filter dates to show only a certain range. Click the column header with dates, go to Data, and select AutoFilter. A small dropdown arrow appears in the column header. Click it, select Date Filters, and choose options like After, Before, or Between. Enter the dates you want to see, and Excel hides all other rows temporarily.

Common mistakes when entering dates

The most common problem is typing a date in a format Excel does not recognize. If you type 15/01/2024 in a region where Excel expects month-first dates, it may treat it as text instead of a date. Check your system's regional settings or use an unambiguous format like January 15, 2024.

Another mistake is leaving dates as text when you need to calculate with them. If a date is stored as text, formulas and sorting will not work correctly. You can tell a date is text if it is left-aligned in the cell (dates are usually right-aligned). To fix this, delete the cell content and re-enter it in a standard date format, or use the Data menu and select Text to Columns to convert a column of text dates to real dates.

Avoid typing dates with a leading apostrophe ('1/15/2024), even though it looks the same. The apostrophe forces Excel to treat it as text, and you will not be able to sort or calculate with it.

Using date functions for more complex tasks

Beyond =TODAY() and =NOW(), Excel has other date functions for specific tasks. =MONTH(A1) extracts just the month number from a date in A1 (1 for January, 2 for February, and so on). =YEAR(A1) extracts the year. =DAY(A1) extracts the day of the month.

If you need to find the last day of a month, use =EOMONTH(A1,0), where A1 is any date in that month. Change the 0 to 1 to get the last day of the next month, or -1 for the last day of the previous month. These functions are useful when you are building a spreadsheet that tracks important date or recurring events.

Frequently Asked Questions

Why does Excel show my date as a number like 45000?

The cell is formatted as a number instead of a date. Right-click the cell, select Format Cells, choose Date from the Category list, pick a date format, and click OK. The date will display normally.

Can I add a time to a date in the same cell?

Yes. Type the date and time together, separated by a space: 1/15/2024 2:30 PM. Excel recognizes this as a date-time value. Format it using Format Cells and choose a format that includes both date and time, such as 1/15/2024 14:30.

How do I calculate someone's age from their birth date?

Use =DATEDIF(A1,TODAY(),"Y"), where A1 is the birth date. The "Y" means years; use "M" for months or "D" for days. This formula calculates the difference between the birth date and today, giving you the age in whole years.

What if my dates are in a different order, like day-month-year?

Excel uses your computer's regional settings to guess the date format. If it guesses wrong, type dates in an unambiguous format like 15-Jan-2024 or January 15, 2024. You can also change your system's date settings in your computer's control panel, though this affects all programs.

Can I copy a date formula down to many rows at once?

Yes. Click the cell with the formula (like =TODAY()), then drag the small square at the bottom-right corner of the cell down as far as you need. Excel copies the formula to each row. For =TODAY(), every cell will show today's date. For formulas that reference other cells, Excel adjusts the references automatically.