Exceljet

Quick, clean, and to the point

How to create a custom date format

In this lesson, we'll look at how to create a custom date format. Excel provides a good selection of date codes that can be used to assemble a wide variety of custom date formats.

Let's take a look.

Let's look first at the Date code reference table. This table shows the date codes available for custom date formats. There are several codes each for days, months, and years, including abbreviated and non-abbreviated forms, and the option to add leading zeros to day and month numbers.

Now let's look at the table we need to format. In the left column, we have a list of dates. The other columns show the custom formats we need to create. Let's start by copying the dates across the entire table.

When building custom date formats, it's often easier to apply a date format that is close to what you want to create, as a start. For column C, the existing format already has all of the components we need. We just need to access the Format Cells dialog, click Custom, and rearrange the codes.

Note that Excel displays a live preview as you create the custom format.

We repeat the process for column D. The Long Date format is close to what we want, so let's start with that. Then we click Custom, and modify.

To abbreviate the day of week, we just use three "d's" instead of four. We do the same thing for month—three "m's" will give us a three-letter month abbreviation.

Since the format in column E is simple, let's just build it from scratch. Looking at our reference table, we can see that we need a code for an abbreviated month (three m's) and a code for the day with a leading zero, which is two d's.

In the Format Cells dialog, we select Custom, then just enter: "mmm space dd" and click OK.

Course 
Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas