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.
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.
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?
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 ..
Hi Manoj,
You can see the formula I used to do what you need here: bit.ly/manoj-formula.