How to quickly create a date format using a custom formula in Excel

By adding a custom formula to a range of cells, you can create a date, without having to manually format each cell (This also known as suffixing numbers with text strings). Let’s say, you have numbers in a spreadsheet and you wish to display them in a particular date format, you can apply a custom number formula to build a date range. Using this custom text string will help save you time and and make your reports more reader friendly.

In today’s tip we’ll show you how it can be done in 4 easy steps:

Download the workbook to practice

 Applies To: Microsoft Excel  2010 and 2013

1. Select the data range D4:D20, and then right click

1. Excel Tip_Suffix String

2. Select Format Cells.

3. Select Custom from the Category box, and then select 0 under Type.

2. Excel Tip_Suffix String

4. Enter the following: 0 “May 2015” under Type, then select OK.

NB: Should your Excel settings differ, remove the spacing as follows: 0” May 2015”

3. Excel Tip_Suffix String

Your selected data should now display the new date format as per below.

4. Excel Tip_Suffix String