How to Format Months, Days, and Years in Excel

Jul 28th, 2015 / Data Analysis / , , ,

Does your file include any dates? If so, check out these simple formatting tips so that your dates are displayed in the style you want.

Selecting Your Preferred Date Format

If your spreadsheet contains dates, you can select either the Short Date or Long Date format.
Ann K. Emery on displaying long dates or short dates in Microsoft Excel

Separating Out the Date’s Month, Day, and Year

Sometimes I need to parse out my date. I might only be interested in the specific month, or the specific day, or the specific year—rather than the entire date.
In these situations, we can use Excel’s =month(), =day(), and =year() functions.
To find the date’s month, type =month( and then click on the cell that contains the full date (like A2 in this example). Then, add a closing parentheses to the end of the function and press the Enter key on your keyboard. Excel will give you a 1 to indicate that the full date’s month is January.
To find the day of the month, type =day( and then click on the cell that contains the full date (again, cell A2). Add a ) to complete the function and click Enter. Excel gives you a 1 to indicate that January 1 is the first day of the month.
Finally, to separate out just the year from the full date, type =year( and click on the cell that contains the full date you’re interested in (cell A2). You know the drill: Add another parentheses to complete the function, press Enter on your keyboard, and Excel will give you a value of 2015.
Ann K. Emery on Microsoft Excel's month, day, and year formulas

Figuring Out the Length of Time Between Two Dates

Did you know that Excel stores semi-recent dates as numbers? January 1, 1900 is actually stored as a 1 behind the scenes in Excel which means that January 1, 2015, which comes 42,005 days later, is stored as 42,005.
This cool feature allows you to perform basic addition and subtraction with dates. Let’s pretend you want to figure out how long an employee worked at your organization. You can use subtraction: the Last Day of Employment minus the First Day of Employment equals the Length of Employment.
Ann K. Emery on figuring out the number of days that took place between two dates in Excel

Auto-Filling Dates

I bet you’ve got better things to do with your time than to type Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec every time you want to see a list of month abbreviations. Excel to the rescue! Type at least the first three abbreviations (Jan, Feb, Mar). Then, highlight those three cells. Scroll your cursor over the tiny square in the lower left-hand corner of the box surrounding the three highlighted cells and drag the tiny square downwards to auto-fill the remaining month abbreviations.
Ann K. Emery on auto-filling dates (Jan, Feb, Mar) into your spreadsheet
Would you rather have a list of the full month names, rather than the abbreviations? Type January, February, March into A1, A2, and A3. Highlight those cells and drag them downwards to fill in the remaining nine months.
Ann K. Emery on auto-filling dates (January, February, March) into your spreadsheet
 
Want to learn more spreadsheet secrets? Check out my 3-hour webinar on August 10 & 17. Space is limited to the first 30 registrants.
 

Related Courses

From spreadsheet stress to superstardom in just 12 weeks. Data analysis techniques & time-saving secrets for busy number-crunchers.
$99

Enroll Now

SPONSORED

#f-post-el-35{display:none !important}

#f-post-el-35{display:none !important}
#f-post-el-35{display:none !important}

Enroll Now