Do you want to know how to format dates in Excel? Do you want to work with date and time a lot better in Office Excel? Learning the formulas for dates in Excel will make your work on a spreadsheet a whole lot easier! To learn more about Excel date formats and time functions that’ll work with any version of Excel, keep reading below!
Excel Dates | How to Format Dates in Excel
Open a new Excel blank spreadsheet. Click on the very top left cell, known as Cell A1. Type your birthdate in a month, day, year format using a forward slash between the serial numbers, such as:
Then press “Enter” to enter the information in that cell. When you press Enter, your cell will move down to the next cell. How does Cell A1 look? First, you’ll notice that Excel lines up the date with the right edge of the cell. Numbers go right, and “text” or words go left. You may always change the alignment from the Home tab>Alignment section.
Depending on your computer, your date may look like one of the following:
- 1 Feb
- 1 Feb 1951
Wait a minute! Most of these make sense, but how can a date be “18660?” PCs track dates by counting from the start date of January 1, 1900. If you count the days between January 1, 1900, and the date we entered, February 1, 1951, there are 18,660 days. I’m assuming Excel is correct; I didn’t count them personally. Oh, and FYI – Macintosh computers sometimes count days differently.
Go back up to Cell A1 by clicking on it. In all my lessons, I say “click” when I mean for you to click the left button on your mouse, and “right-click” to click the correct button. When you click on a cell, a green rectangle surrounds the cell to show that it is active.
Notice just above the “A” through “Z” column headings. I circled this “Name Box” in red. The “You Are Here” spot on the map. It will tell you which cell you have selected, Cell A1.
The “Formula Bar” is circled in yellow. This shows you how Excel sees your date without any formatting changes. We’re going to make several to how the date is displayed inside Cell A1, but the contents of will always look like that on the Formula bar.
Formatting a Date in Long Date Format
Excel is all about numbers. At first, the Home tab or ribbon looks very similar to other Microsoft products, like Word or PowerPoint, but it has a special Number section. When you entered your date of birth in cell A1, Excel guessed it was a date and used a date format. In the figure above, I circled the word “Date” in blue at the top of the Home tab>Number section. This box tells you which format is selected. Click the Number Format drop-down arrow next to the word “Date.”
The number format drop-down menu or drop-down list shows you all the most commonly used number formats. Right now, click on “Long Date.”
Look at your date of birth now! It shows you the day of the week. Did you know you were born on that day? Notice that column A got wider, just big enough to fit the “Long Date” format. The information in the Name Box and Formula Bar haven’t changed because the date is still the same date to Excel, it’s just in a different format.
Formatting a Date in MM/DD/YY Format
My favorite date format is a two-digit month, two-digit date, and two-digit year, aka, “MM/DD/YY” format; “M” is for “month;” “D” is for “date;” “Y” is for “year.” It’s my favorite date format because it helps the dates line up in a vertical column. With Cell A1 still selected, click the Home tab>Number section>Format drop-down arrow next to the word “Date” again. This time, I want you to select the very last item, “More Number Formats.”
The Format Cells window or dialog box will appear. You can also get to this window by clicking the Home tab>Number section>More choices arrow – the small arrow at the bottom right corner of the Number section. Remember there is usually more than one way to issue a command in Microsoft Office.
Excel likes to guess what you want, so it automatically takes you to the Date formats in the Format Cells window. Scroll down to the “03/14/12” date format; this is the “MM/DD/YY” format I like. Notice that there is a “Sample” section in the Format Cells Window that shows you how Cell A1 will look if we click OK. If it’s okay, click OK.
Now our date is in our preferred format. The Name Box and Formula Bar did not change, but the date now reads in this format: “02/01/51.”
Change the Date, Not the Format
We don’t want our birthdate on a spreadsheet, so let’s change it to today’s date. You don’t need to erase or delete your date of birth. Simply click on Cell A1 and type today’s month and date with a slash between. Do not include the year and press Enter to “enter” the date into Cell A1. When you pressed Enter, it removed the old information in Cell A1, your birthdate, and replaces it with the new information, today’s date.
Because we have already formatted Cell A1, today’s date will now appear in our “MM/DD/YY” format. Because we didn’t type in the year, Excel guessed we meant this year. This is a very handy feature and saves a lot of typing except for one time during the year. During tax season, you are entering numbers for the previous year. I always forget to type in the previous year, and always have to go back and make changes to my tax spreadsheets. Always.
Formula for “=TODAY()” and “=NOW()”
If we type in an exact date, Excel will always remember that specific date. Usually, for spreadsheets, we want the current date on reports, right? In our MS Word lesson on dates (insert link), we used the Insert tab. In Excel, we enter a formula. Go back to Cell A1 and type the following formula: =today()
Every time you start a cell’s contents with the equal sign, Excel knows you are entering a formula in that cell. “=Today()” is how Excel calculates today’s date. You can type the formula in lower case, uppercase or a mix of both. Excel doesn’t pay attention to a case in formulas. Press Enter and Excel will put today’s date in the cell.
It will look just like it did when you typed the date, except for one very important difference! Click back on Cell A1 and then look at the Formula Bar. Today’s date appears in Cell A1, but our formula “=TODAY()” appears in the Formula Bar.
For a similar formula, go to Cell A2 which you have not formatted, and type: =now()
The “=NOW()” formula enters the current date and time. Use the “More Number Formats” you learned above to play with formatting the date and time. You can add seconds or remove the hours and minutes. It’s all up to you and which format you select.
If you save this spreadsheet right now and open it again tomorrow, both the “=TODAY()” and “=NOW()” dates will be current.
Did this article on excel dates help? Let us know in the comments section below.