How to Convert Dates to Numbers in Microsoft Excel

When you use dates in your Microsoft Excel workbooks, there may be a time when you want the serial numbers for those dates instead. You might use them in formulas or for calculations rather than the dates themselves.

We’ll show you how to convert dates to numbers, specifically serial numbers, in Excel. The method you use depends on how Excel stores dates in your worksheet; basically, how they are formatted.

Determine How Your Dates Are Formatted

Before you use one of the methods below to convert your date to a serial number, you need to determine how the date is formatted. By default, when you enter a date in Excel, it’s formatted as a date which is technically a number value. However, there may be instances where you enter, convert, or import dates that end up formatted as text values.

You can easily select the cell containing the date, head to the Home tab, and look at the Number Format drop-down box to see the current format of the cell. As you can see here, our date is formatted as a Date (number).

However, if you have many dates that you plan to convert, the above method can be tedious. Plus, if you have something random, for example, an apostrophe in front of the date, it may display in the Date format but be considered text when you try to convert it.

Instead, two simple Excel functions can help you.

Using the ISNUMBER Function

The ISNUMBER function lets you know if your data is formatted as a number and ISTEXT lets you know if it’s formatted as text. Depending on which you want to use, you’ll receive a True or False result.

To see if the dates in cells A2 through A13 are formatted as numbers, you’d use the following formula:

=ISNUMBER(A2:A13)

You can see here we receive a True result for those dates that are formatted as numbers and False for the one that is not.

Using the ISTEXT Function

If you prefer to see if your dates are formatted as text, use this formula:

=ISTEXT(A2:A13)

Like the other function, here we can see True for those dates formatted as text and False for the one that is not.

You can also use these functions to check a single cell with this formula if you like:

=ISTEXT(A2)

Once you determine the formatting for your date, you can use the corresponding method below to convert it to a serial number.

Convert a Numeric Date to a Serial Number

If you find that your date is formatted as a numeric value, you can simply change the format to display the serial number.

  1. Select the cell or cell range and go to the Home tab.
  2. Open the Number Format drop-down list and pick Number.
  1. You should see the dates in your selected cells as the serial numbers.
  1. You can then keep the cells selected and use the Decrease Decimal button in the Number section of the Home tab to remove the decimals.

Convert Dates to Numbers Without Decimals

If you prefer, you can convert the dates and remove the decimals in one fell swoop.

  1. Select the cells containing the dates, right-click, and pick Format Cells. Alternatively, use the keyboard shortcut Ctrl + 1 on Windows or Command + 1 on Mac.
  2. In the Format Cells dialog box, go to the Number tab and choose Number on the left.
  1. You’ll see a preview of the serial number at the top. Change the Decimal Places setting to 0 and select OK to save the change.
  1. You’ll then see your dates changed to serial numbers without decimals.

Convert a Text Date to a Serial Number

If you determine your date is using a text format, you can use a handy Excel date function. With the DATEVALUE function, you can quickly convert text dates to serial numbers.

The syntax is DATEVALUE(text) where you can use a cell reference, range, or exact date as the argument.

In this example, we’ll convert the cell range A2 through A13 from text dates to numbers with this formula:

=DATEVALUE(A2:A13)

As you can see, we have serial numbers for each date in our cell range. You may then want to remove the original dates or replace them by dragging the serial number range over them, per your preference.

To convert a single cell, use this formula replacing the cell reference with your own:

=DATEVALUE(A2)

To convert a specific date using the function, place the date within quotation marks as in these formulas:

=DATEVALUE(“1/1/2024”)

=DATEVALUE(“1-JANUARY-2024”)

=DATEVALUE(“2024/01/01”)

If you have trouble with your Excel formula, check out the possible causes and solutions.

In Excel, converting dates to numbers, specifically serial numbers, takes only a few minutes using these methods. For related tutorials, look at how to sort by date in Excel.



Source : How to Convert Dates to Numbers in Microsoft Excel

Comments

Post a Comment

Popular posts from this blog

How to Add or Remove a Secondary Axis in Microsoft Excel Charts

Image
When you have a chart with mixed data types or a dataset that varies broadly between series, consider using a secondary axis in Microsoft Excel. With it, you can display the data you need without overwhelming or confusing your viewers. When to Use a Secondary Axis As mentioned, if you have different data types or your series amounts widely vary, these are good times to use a secondary axis. For instance, you can see that the column chart below doesn’t accurately depict the visitors and sales in our dataset. There’s a large variance in the sales amounts, making it hard to get a decent picture of the number of visitors per month. Plus, the sales are in dollar amounts, whereas the visitors are basic number values . If your chart would benefit from a secondary axis like this one, here’s how to add one as well as remove a secondary axis later if you change your mind or your data. How to Add a Secondary Axis in Excel To add a secondary axis to your Excel chart, you’ll use the combo ...
Read more

How to Use and Customise the Status Bar in Microsoft Excel

Image
Excel’s status bar is one of the hidden gems (and an unsung hero) that makes it the default spreadsheet software you turn to. It quietly resides at the bottom of the Excel window, often overlooked but brimming with valuable information and customization options. In this article, we will look at it and show you how to take advantage of it. Excel Status Bar Default Options Even before you customize the Excel status bar, it’ll display some helpful information and give you access to some useful functionalities. The common Excel options you can find as default on the status bar are: Cell Mode Macro Recording Tool Accessibility Check Page Number Statistics/Mathematical Options Worksheet Views Zoom Options Let’s see what each of them does and how you can use them. Cell Mode At the leftmost side of the Excel status bar, you’ll find the cell mode indicator. It’ll inform you about the status of the currently selected cell. There are four different status modes: Ready . It ...
Read more

What Is Windows AI-Powered Copilot?

Image
Microsoft has released its brand-new artificial intelligence (AI) companion, Microsoft Copilot. Previously known as Bing Chat, Copilot has a series of new abilities that can help you get more done in less time. In this article, we’ll provide a brief description of each of Microsoft’s new AI copilots, then we’ll focus on the Windows 11 copilot and what it can do to boost your productivity. What Is Microsoft Copilot? Microsoft Copilot is an artificial intelligence chatbot you can ask to perform different tasks, including generating text and images and answering general questions. Microsoft’s plan is to have a Copilot in each of its apps to provide assistance and improve user experience across the Microsoft ecosystem. Here are the different Copilots that are currently available or slated for future release: Windows 11 Copilot: Available for free as a preview in North America, Asia, and South America, Windows 11 Copilot lets you perform tasks, get answers, and create AI-generate...
Read more