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.
Want to learn more spreadsheet secrets? Check out my 3-hour webinar on August 10 & 17. Space is limited to the first 30 registrants.
Nicole Clark, LMSW
Aug 5, 2015 -
This is really great, Ann! Thanks for the refresher!