How to Set a Date in Excel

This wikiHow teaches you different ways to insert dates into your Microsoft Excel spreadsheet.

Method 1
Method 1 of 4:

Formatting a Value As a Date

  1. Image titled Set a Date in Excel Step 1
    1
    Type the desired date into a cell. Double-click the cell in which you want to type the date, and then enter the date using any recognizable date format. You can enter the date in a variety of different formats.[1]
    • Using January 3 as an example, some recognizable formats are "Jan 03," "January 3", "1/3," and "01-3."
  2. Image titled Set a Date in Excel Step 2
    2
    Press the ↵ Enter key. As long as Excel recognizes the date format, it will re-format the cell as a date, which is usually mm/dd/yyyy or dd/mm/yyyy, depending on your locale.
    • If the text automatically aligned to the right, then Excel recognized it as a date and re-formatted it.
    • If the text stayed aligned to the left, Excel is treating the input as text rather than a date. This could be because it cannot recognize your input as a date, or because that cell's format is set to something besides a date.
    Advertisement
  3. Image titled Set a Date in Excel Step 3
    3
    Right-click the date cell and select Format Cells. A new window will pop up.
  4. Image titled Set a Date in Excel Step 4
    4
    Click the Number tab. It's the first tab.
  5. Image titled Set a Date in Excel Step 5
    5
    Select Date in the "Category" panel. A variety of date formats will appear on the right side of the window.
  6. Image titled Set a Date in Excel Step 6
    6
    Select your desired date format under "Type." This reformats the selected cell to display in this format.
    • You can also change your locale to access date formats used in your location.
  7. 7
    Click OK. The selected cell(s) will now display dates in the selected format.
    8. Advertisement
Image titled Set a Date in Excel Step 7
Method 2
Method 2 of 4:

Inserting the Current Date

  1. Image titled Set a Date in Excel Step 8
    1
    Click the cell in which you want today's date to appear. This can be in an existing formula, or in a new cell.
  2. Image titled Set a Date in Excel Step 9
    2
    Type an equal sign = followed by the formula TODAY(). If you wish to retrieve the current time as well, use NOW() instead of TODAY().[2]
  3. Image titled Set a Date in Excel Step 10
    3
    Hit ↵ Enter. Excel will return today's date as the cell value. This is a dynamic date, meaning it will change depending on when you are viewing the sheet.
    • Use the shortcuts Ctrl + ; and Ctrl + Shift + ; instead to set a cell's value to today's date and time respectively as a static value. These values will not update, and act as a timestamp.
    4. Advertisement
Method 3
Method 3 of 4:

Using the DATE Function

  1. Image titled Set a Date in Excel Step 11
    1
    Click the cell in which you want to type the date.
  2. Image titled Set a Date in Excel Step 12
    2
    Type an equal sign = followed by the date formula DATE(year, month, day). Year, month, and day should be numerical inputs.
  3. Image titled Set a Date in Excel Step 13
    3
    Hit ↵ Enter. Excel will return the default date format, which is usually mm/dd/yyyy or dd/mm/yyyy depending on your locale.
  4. Image titled Set a Date in Excel Step 14
    4
    Expand on the formula if required. You can set formulas for the year, month, and day values. Or, you can use the DATE function within other formulas.
    • For instance, DATE(2010,MONTH(TODAY()),DAY(TODAY())) sets the cell's value as today's month and day in 2010. The formula DATE(2020,1,1)-10 sets the value to 10 days before 1/1/2020.
    5. Advertisement
Method 4
Method 4 of 4:

Filling in Dates Using Fill Series

  1. Image titled Set a Date in Excel Step 15
    1
    Type the desired date into a cell. Double-click the cell in which you want to type the date, and then enter the date using any recognizable date format. You can enter the date in a variety of different formats.
    • Using January 3 as an example, some recognizable formats are "Jan 03," "January 3", "1/3," and "01-3."
  2. Image titled Set a Date in Excel Step 16
    2
    Hit ↵ Enter. Excel will re-format the cell and align the text to the right if it has recognized it as a date.
  3. Image titled Set a Date in Excel Step 17
    3
    Select all the cells you wish to fill with dates. Include the cell in which you just entered the date. To select, drag your mouse over all the cells, select an entire column or row, or hold Ctrl (PC) or ⌘ Cmd (Mac) while clicking each cell.
  4. Image titled Set a Date in Excel Step 18
    4
    Click Fill on the Home tab. It's at the top of Excel in the "Editing" section and looks like a white box with a blue down arrow.
  5. Image titled Set a Date in Excel Step 19
    5
    Click Series…. It is near the bottom.
  6. Image titled Set a Date in Excel Step 20
    6
    Select a "Date unit." Excel will use this to fill the blank cells based on this setting.
    • For instance, if you select "Weekday," all the blank cells will populate with the weekdays following the initial input date.
  7. Image titled Set a Date in Excel Step 21
    7
    Click OK. Make sure the are dates filled in correctly.
    8. Advertisement

You Might Also Like

Make a Spreadsheet in Excel
How to
Make a Spreadsheet in Excel
Unprotect an Excel Sheet
How to
Unprotect an Excel Sheet
Create a Mortgage Calculator With Microsoft Excel
How to
Create a Mortgage Calculator With Microsoft Excel
Create a Graph in Excel
How to
Create a Graph in Excel
Create a Timeline in ExcelHow to Create a Timeline in Excel: SmartArt, Templates, and More
Change from Lowercase to Uppercase in Excel
How to
Change from Lowercase to Uppercase in Excel
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Unhide Rows in Excel
Convert Excel to Word3 Easy Ways to Convert Microsoft Excel Data to Word
Merge Cells in ExcelHow to Merge Cells in Microsoft Excel: A Quick Guide
Use ExcelNew to Excel? Here's Super Easy Tricks to Get You Started
Calculate Age on ExcelHow to Calculate Age on Microsoft Excel Using Functions
Find Duplicates in Excel
How to
Find Duplicates in Excel
Link Sheets in Excel
How to
Link Sheets in Excel
Advertisement

References

  1. ↑ https://support.office.com/en-us/article/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
  2. ↑ https://support.office.com/en-us/article/insert-the-current-date-and-time-in-a-cell-b5663451-10b0-40ab-9e71-6b0ce5768138

About This Article

KD
Written by:
Katie Double
Tech Specialist
This article was written by Katie Double. Katie Double is a Technology Writer and Editor for wikiHow. She has over five years of experience in technical support, IT process analysis, and quantitative research. Katie loves exploring the application of various technological tools, from data analysis and visualization to music production and performance. Though originally from Denver, Colorado, she currently resides in New Zealand. She has a BA in Psychology from the University of Colorado Boulder. This article has been viewed 12,545 times.
How helpful is this?
Co-authors: 3
Updated: April 12, 2020
Views: 12,545
Categories: Microsoft Excel
Article SummaryX

1. Click on a cell.
2. Type in a date.
3. Hit Enter.
4. Review the date format. Change by right-clicking and selecting Format Cells.

Did this summary help you?
Advertisement