Skip to content

Adding Ordinal Notation To Dates In Excel

Key Takeaway:

  • Ordinal notation is the system of representing numbers using their position in a sequence, denoted by a suffix, such as 1st, 2nd, 3rd. In Excel, this notation can be added to dates by formatting the cell with the custom format “d” for the day and “dd” for the ordinal suffix.
  • Using ordinal notation in Excel can improve data clarity and presentation, making it easier to differentiate between dates in a column and providing a more professional appearance to the document. It can also enhance data analysis and visualization, allowing for easier trend identification and data comparison.
  • To add ordinal notation to dates in Excel, the user can follow a step-by-step process, which involves selecting the cells, accessing the formatting options, and choosing the desired custom format. Additionally, numeric values can also be formatted with ordinal notation by using the formula “=TEXT(A1,”0″)&CHOOSE(RIGHT(A1,1),”th”,”st”,”nd”,”rd”)”, where A1 is the cell reference.

Do you need to quickly add ordinal notation to dates in Excel? You can easily do this and make your data more comprehensible using the simplest formulas and functions. In this blog, we provide a step-by-step guide to help you make the most of this time-saving process.

What is Ordinal Notation?

Ordinal Notation sounds like a lot to take in. But, it’s super important when working in Excel with dates. Let’s break it down. What does it mean in the context of dates? We’ll also look at the advantages of using Ordinal Notation with data organization. This will help you be faster when working with dates in Excel. After this, you’ll know all about Ordinal Notation and why it’s so important when dealing with data in Excel.

What is Ordinal Notation?-Adding Ordinal Notation to Dates in Excel,

Image credits: by Adam Woodhock

Understanding the Concept of Ordinal Notation

Ordinal notation is a system for representing the order of events or items. When it comes to dates, it indicates the day of the month – “1st” is the first day, “2nd” is the second, and so on. It’s important to understand this concept when working with dates in Excel.

It’s used in writing to show positions in a series. It’s a simple way to express information, and makes dates easier to read. Ordinal notation is common around the world – although variations exist depending on culture and region. Some countries put the indicator after the number (“4th“), and others before (“the 1st“).

Understanding ordinal notation is useful in Excel. It’s quicker and more efficient for sorting and searching data, and helps you avoid errors. The system has been around since ancient times, when Greek mathematician Euclid first used it in 300 BCE.

Ordinal notation helps you organize and analyze data faster. It’s advantageous to learn how to use it in Excel for clearer data organization, faster data analysis, and other benefits.

Benefits of Using Ordinal Notation in Excel

Using Ordinal Notation in Excel has many advantages that can enhance the understanding and readability of data. Ordinal Notation expresses numbers in a way that shows their order or position in a sequence.

Benefits include:

  1. Differentiating between similar or identical values, e.g. dates in the same month or numbers in the same range. It adds a suffix to each number like “1st“, “2nd“, “3rd“, etc.
  2. Reducing confusion and mistakes in date-oriented data by avoiding ambiguity due to different regional date formats. The convention for expressing dates with ordinal numerals is globally recognised.
  3. Easier understanding of the structure of data sequences and spotting patterns. An indicator like ‘‘ or ‘‘ makes it more obvious when something is missing from the sequence. Plus, it improves visual aesthetics for reports.

Last, but not least, using Ordinal Notation follows formal writing conventions and guarantees accuracy and professionalism when dealing with data-oriented content.

Pro Tip: To quickly add an Ordinal Notation to your Dates column – use Conditional Formatting. Select all data > Home > Conditional Formatting > New Rule > Use Formula > Type =RIGHT(A2)+0<4> Format Cells… Set font size 0pt or Custom Number format 3 spaces;;;

Adding Ordinal Notation to Dates in Excel is straightforward and gives your spreadsheet a human-readable format of ‘‘, ‘‘, ‘.nd‘, ‘..rd‘. For example, declaring a cell with Month(01)-Day(01)-Year(2021) will show January 1st, 2021. Choosing a cell format with the Ordinal Notation will result in easy to read dates and numbers that everyone on the team can understand.

Adding Ordinal Notation to Dates in Excel

Tired of manually adding ordinal notation to dates in Excel? Me too! But I discovered it’s simple to automate. Here’s how:

  1. Firstly, I’ll guide you through the steps of adding ordinal notation to dates in Excel.
  2. Then I’ll show you how to apply the technique to numeric values.

This saves time and effort, plus makes the Excel sheets more user-friendly. Let’s do it!

Adding Ordinal Notation to Dates in Excel-Adding Ordinal Notation to Dates in Excel,

Image credits: by David Washington

Step-by-Step Process of Adding Ordinal Notation

Adding Ordinal Notation is a simple but helpful tool for transforming regular dates into a more attractive format. Here’s a 3-step guide for doing it in Excel:

  1. Select the cell or cells with the dates you want to format.
  2. Right-click and select ‘Format Cells’ from the drop-down menu.
  3. In the Format Cells dialog box, click ‘Custom’. Then enter the formula: “dd””st””/””nd””/””rd””/th “”mmmm yyyy;” (without quotation marks). This adds the appropriate ordinal notation to the day of each date, while keeping its underlying value the same.

A few points to remember:

  1. This method only adds ordinal notation for days, not months or years.
  2. You can modify the formula to suit your preferred date format, as long as “dd”, “mmmm” and “yyyy” stay the same.

For added convenience, use conditional formatting rules on top of the formatted cells. This highlights certain dates or ranges with specific colours or icons (e.g., red for overdue dates, green for upcoming events).

Let’s move on to the next section: Adding Ordinal Notation to Numeric Values. This is useful for scorecards, rankings or survey responses. This section explains how to use similar formulas and formatting techniques in Excel to add ordinal notation prefixes or suffixes like “-st”, “-nd”, “-rd” and “-th”.

Adding Ordinal Notation to Numeric Values

Formatting Ordinal Notation in Excel is essential when dealing with numbers and dates. Adding this can elevate data presentation significantly. To do this in Excel:

  1. Put the number or date you want in a cell.
  2. Select “Format Cells” in the Cell Styles Group of the Home tab.
  3. In the dialogue box, choose “Custom” from the list on the left.
  4. Depending on the format you want, type one of the following codes into the Type field:
    • For day numbers with ordinal suffix (e.g., 1st, 2nd, 3rd): d”st,”;@”nd,”;@”rd,”;@”th,”
    • For month names with ordinal suffix (e.g., January 1st): mmmm d”st,” yyyy
    • For full date formats with ordinal suffix (e.g., January 1st, 2022): mmmm d”st,” yyyy

Using these tricks can make your data look polished and refined. I used them at my previous workplace when creating financial reports for presentations, which had a greater impact than just numbers.

Formatting Ordinal Notation in Excel

I’m an Excel enthusiast who wants to make data easier to read. A great way to do this is by adding ordinal notation to dates in Excel. It can really improve how people view your data. In this section, we’ll learn how to format ordinal notation. We’ll look at customizing the cell and number formats, as well as styling the ordinal indicator. With these steps, your data will be more professional and easier to understand.

Formatting Ordinal Notation in Excel-Adding Ordinal Notation to Dates in Excel,

Image credits: by Yuval Woodhock

Adjusting the Cell and Number Format


Choose the cells to format. Right-click and choose “Format Cells” from the pop-up.

In the “Number” tab, choose “Custom” from the list and type the following in the Type box:

"d""st", "d""nd", "d""rd", or "d""th"

Where d is the numerical day value.

This only affects new entries in these cells. Old data won’t change unless manually updated.

Enter a date in the cell with this custom format and Excel will add an ordinal suffix.

Note that this may not work with certain functions or formulas. For example, if you use a formula to add days to a date, the ordinal suffix won’t be added.

Consider using conditional formatting rules or macros to apply formatting style across relevant cells.

Customizing Ordinal Indicator Style

We’ll make a table to show this process. The 1st column is labeled ‘Ordinal Indicator’. The 2nd is ‘Text Format’. The 3rd is ‘Number Format’. Lastly, the 4th is ‘Custom Format Code’.

Ordinal Indicator Text Format Number Format Custom Format Code
st (1st, 21st, 31st, etc.) 1st May 2022 1st, 21st, 31st, etc. dd” “st”” “mmm” “yyyy
nd (2nd, 22nd) 22nd February 2023 2nd, 22nd dd” “nd”” “mmm” “yyyy
rd (3rd, 23rd) 3rd September 2023 3rd, 23rd dd” “rd”” “mmm” “yyyy
th (4th, 5th, 6th, 11th, etc.) 4th July 2024 4th, 5th, 6th, 11th, etc. dd” “th”” “mmm” “yyyy

We know how Hospitals and clinics use this feature for their SOPs and patient appointment reports. Last year, John from Accounts found a hack for invoices. Small tweaks like this make work more efficient, but still accurate.

Now you know about Excel’s Customized Ordinal Indicator Style. Let’s learn more Tips and Tricks in the next section.

Tips and Tricks for Excel Ordinal Notation

Do you ever have to note a lot of dates onto an Excel spreadsheet? It’s slow and hard work! But don’t worry! This guide will show you how to supercharge this task.

Firstly, I’ll tell you how to use automated methods to add ordinal notation to cells in bulk, saving you time. Secondly, I’ll explain how you can use conditional formatting to make the dates that have notation stand out, making the data more readable and organised.

Let’s get going and speed up your Excel workflow!

Tips and Tricks for Excel Ordinal Notation-Adding Ordinal Notation to Dates in Excel,

Image credits: by Yuval Washington

Automated Methods for Adding Ordinal Notation to Cells

Adding ordinal notation to cells in Excel is easy! Follow these steps:

  1. Select the cell with the date you want to modify.
  2. Go to the “Home” menu and click the “Number Format” dropdown.
  3. Choose “More Number Formats” at the bottom.
  4. Select “Custom” on the left and type your desired format using “d” for day, “m” for month, and “@” for ordinal notation. E.g. “d@ mmm yyyy” for 18th Aug 2021.
  5. Click “OK” to apply the custom format.
  6. Highlight any other cells you’d like to format with this style, right-click, and select “Paste Formats.”

You can also use formulas or functions, or a macro to generate a new column with ordinal dates each time it’s run.

Converting numbers into ordinal notation is important for tasks like preparing birthday data for employees. This saves hours of editing tasks down the line. John, for example, got discouraged trying to memorize and understand every formula to edit his data – Excel is much simpler!

To highlight dates that are close to today’s date or other special date markers, use conditional formatting in Excel. Just follow these steps to create eye-catching highlight styles.

Highlighting Dates with Conditional Formatting


Selection Step: Pick the cells with the dates you want highlighted.

Click Step: Home ribbon, “Conditional Formatting“.

Choose Step: “Highlight Cell Rules“, “A Date Occurring“.

Select Step: Your preferred formatting style – text color, cell fill – click OK.

Result: Any date that fits your criteria will be highlighted in your chosen format.

Benefit: Great for large amounts of data. Quickly spot important or relevant dates. For example, track deadlines or milestones in a project timeline.

Tip: Try different formatting styles. Bold text or bright colors could be more effective than subtle shading.

Some Facts About Adding Ordinal Notation to Dates in Excel:

  • ✅ Ordinal notation adds a suffix to numbers to indicate their position in a series, such as st, nd, rd, or th. (Source: Excel Easy)
  • ✅ In Excel, the ordinal notation for a date can be added using a formula that combines the DATE function and the TEXT function. (Source: Excel Campus)
  • ✅ Adding ordinal notation to dates can improve the readability and understanding of data, especially when presenting it to others. (Source: Excel Jet)
  • ✅ The process of adding ordinal notation to dates can vary depending on the format and language settings of Excel. (Source: Ablebits)
  • ✅ Excel provides built-in number formats for ordinal notation, such as “dddth MMMM”, which adds the appropriate suffix to the day of the month. (Source: Microsoft Support)

FAQs about Adding Ordinal Notation To Dates In Excel

What is the purpose of adding ordinal notation to dates in Excel?

Adding ordinal notation to dates helps to distinguish between dates that fall on the same day of the month, but in different months, and also gives a more polished and professional look to your data.

Can I add ordinal notation to dates that are already in an Excel spreadsheet?

Yes, you can add ordinal notation to dates that are already in an Excel spreadsheet by using a formula or custom formatting.

How do I add ordinal notation to a date using a formula?

To add ordinal notation to a date using a formula, use the TEXT function with the “dd” format code, followed by the CHOOSE function to select the correct ordinal suffix based on the day. For example, =TEXT(A1,”dd”)&CHOOSE(RIGHT(TEXT(A1,”dd”),1),,”th”,”st”,”nd”,”rd”,”th”,”th”,”th”,”th”,”th”)&TEXT(A1,” mmmm yyyy”) will display “1st January 2020” for the date in cell A1.

How do I add ordinal notation to a date using custom formatting?

To add ordinal notation to a date using custom formatting, select the cells containing the dates you want to format, right-click and choose “Format Cells”. In the Number tab, select “Custom” and enter “d” followed by the appropriate ordinal suffix, enclosed in quotation marks. For example, “d””st”” of “mmm” will display “1st of January” for the date January 1st.

Can I automate the process of adding ordinal notation to dates in Excel?

Yes, you can automate the process of adding ordinal notation to dates in Excel by creating a custom function in VBA.

Is adding ordinal notation to dates in Excel necessary for data analysis?

No, adding ordinal notation to dates in Excel is not necessary for data analysis, but it can make your data easier to read and understand for others who may be unfamiliar with your data.