Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Nov 18 2012

Formatting Dates in Excel

how to format dates in Excel

You’re not limited to the date formats built into Excel. You can format them however your heart desires. The underlying value (the exact date) will not change. You can tell by looking in the Formula Bar when you select a formatted date.

To open the Format Cells dialog, just select the cells you want to format (select the first one, then press Ctrl/Command-Shift-Down Arrow), and press Ctrl/Command-1 if they’re all in the same column, which is the standard layout. Then choose Number > Custom and whatever formatting you want.

format cells in Excel

 

Formatting Options

Here are your formatting options in Excel:

m: Month
mm:
Month with a leading 0
mmm:
Month as first three letters (e.g., Jan)
mmmm:
Month written out (e.g., January)

y: Year
yy:
Year with a leading 0
yyyy:
Year with four digits

d: Day
dd:
Day with a leading 0
ddd:
Day of the week (e.g., Mon)
dddd:
Day of the week written out (e.g., Monday)

Examples

Here are some examples of formatting and how they would look, using the date 8/3/12 (my birthday — feel free to send an early/late present!):

08/03: mm/dd
08/2012: mm/yyyy
Aug. 3, 2012: mmm. d, yyyy
Friday,  August 3rd, 2012: dddd, mmmm d”rd”, yyyy
Fri: ddd
2012.08.03: yyyy.mm.dd

Learn More

If you want to learn more beastly data formatting tips and tricks, check out out my Annielytics Dashboard Course offerings.

For a more comprehensive look at how to use custom number formatting in Excel, check out my Search Engine Land thesis post.

Photo by odelsjenta.

 

Written by Annie · Categorized: Excel · Tagged: Excel Formatting

Comments

  1. Justin Goodman says

    November 28, 2012 at 4:02 PM

    In the example: Friday, August 3rd, 2012: dddd, mmmm d”rd”, yyyy
    Is there a way to have it read “st or nd or rd or th” based on the previous number using formatting?

    Reply
    • Manoj Kumar says

      July 3, 2018 at 3:41 AM

      Please make date formula with example like 23/07/2018 current date formatting, but now required formatting this formatting 20180723, how implement please Sher one to one ..

      Reply
      • Annie Cushing says

        July 3, 2018 at 7:47 PM

        Hi Manoj,

        You can see the formula I used to do what you need here: bit.ly/manoj-formula.

        Reply

Leave a Reply Cancel reply

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

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2023 annielytics.com