6 Comments

  • This is really great, Ann! Thanks for the refresher!

  • Lane King says:

    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?

  • Marilyn says:

    how to format just month and day? it’s a ss list, and we don’t need the year?

    • Ann K. Emery says:

      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.

    • Graeme McMillan says:

      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.

  • Leave a Reply to Nicole Clark, LMSW Cancel reply

    Your email address will not be published. Required fields are marked *

    How to Format Months, Days, and Years in Excel

    Updated on: Jul 28th, 2015
    Data Analysis
    , , ,
    Collage of a computer monitor, laptop, calendar, people and charts..

    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

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

    6 Comments

  • This is really great, Ann! Thanks for the refresher!

  • Lane King says:

    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?

  • Marilyn says:

    how to format just month and day? it’s a ss list, and we don’t need the year?

    • Ann K. Emery says:

      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.

    • Graeme McMillan says:

      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.

  • Leave a Reply to Nicole Clark, LMSW Cancel reply

    Your email address will not be published. Required fields are marked *

    You Might Like

    Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

    Enroll

    How to Bring Your Technical Tables to Life

    Just because I’m pro-graph, I’m not anti-table. Technical tables have so much value, especially as visual appendices for reports. In this blog post, you’ll get ideas for bringing your technical tables to life.

    More »

    Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.

    Enroll

    Subscribe

    Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.