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.
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.
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.
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.
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.
Lane King 
Marilyn 
Graeme McMillan
6 Comments
This is really great, Ann! Thanks for the refresher!
What if I want to abbreviate the long ones, like Jan, Feb, Nov, etc. but don’t want to abbreviate the short ones, like March, April, May, June, July?
Hi Lane,
You’d need to re-code your months with (1) a nested IFs formula or (2) a separate lookup table.
I rarely do this, but… feel free to email me directly — Ann@DepictDataStudio.com. This would be a great how-to example for a future blog post!
Ann
how to format just month and day? it’s a ss list, and we don’t need the year?
What’s your staring point? Do you have full dates (e.g., mm-dd-yy) in their own column? And what’s your desired end point? Share some more details and I’ll help you troubleshoot.
If you have a cell with the date 1/8/2023, then highlight the cell, click on the Home ribbon and find the drop-down for Number Format. Click More Number Formats at the end of the list. Choose Custom category, then type the following options in the ‘Type’ field:
• d-mmm – this will display the date as 1-Aug
• dd-mmm – this will display as 01-Aug
•dd-mm – this will display as 01-08
• dd/mm – this will display as 01/08
• mmmd – this will display as Aug1
Have a play and get any date to display any way you want.