• Skip to main content

Annielytics.com

I make data sexy

  • About
  • Services
  • Tools
  • Blog
  • Portfolio
  • 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 Cushing · Categorized: Excel

Reader Interactions

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 *

Copyright © 2025