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
Month with a leading 0
Month as first three letters (e.g., Jan)
Month written out (e.g., January)

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

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


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

Learn More

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.



Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>