How to Set a Date and Time in Microsoft Excel

PDF download Download Article
PDF download Download Article

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

PDF download Download Article
  1. 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. 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. A new window will pop up.
  4. It's the first tab.
  5. A variety of date formats will appear on the right side of the window.
  6. 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
Method 2
Method 2 of 4:

Inserting the Current Date

PDF download Download Article
  1. This can be in an existing formula, or in a new cell.
  2. If you wish to retrieve the current time as well, use NOW() instead of TODAY() . [2]
  3. 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

PDF download Download Article
  2. Year, month, and day should be numerical inputs.
  3. Excel will return the default date format, which is usually mm/dd/yyyy or dd/mm/yyyy depending on your locale.
  4. 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

PDF download Download Article
  1. 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. Excel will re-format the cell and align the text to the right if it has recognized it as a date.
  3. 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. It's at the top of Excel in the "Editing" section and looks like a white box with a blue down arrow.
  5. It is near the bottom.
  6. 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. Make sure the are dates filled in correctly.
    8. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      • Set a custom date format in Excel by right-clicking a cell, clicking Format Cells , and selecting "Custom" as the category in the Number tab. Review the available date options. Create your own by typing the format code using an existing code.
        Thanks
        Helpful 0 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      You Might Also Like

      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Link Sheets in Excel
      How to
      Make a Spreadsheet in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      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

      Article Summary X

      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?
      Thanks to all authors for creating a page that has been read 39,005 times.

      Is this article up to date?

      Advertisement