Are you wondering how to convert a date to a number in Microsoft Excel? Keep reading!

Whether you’re calculating project timelines, tracking employee attendance, or generating reports with specific date-based metrics, converting dates to numbers in Excel is a must-have skill.

In many real-world scenarios, you’ll need to use dates as numeric values to sort data, filter records, or perform calculations like time intervals.

But here’s the catch! Excel’s date formatting can be tricky. What looks like a regular date on the screen might not behave like a number in your formulas. Without the right method, you might end up with errors or misleading results.

In this tutorial, I’ll show you simple, reliable ways to convert dates to numbers so you can avoid confusion and get the job done fast. Follow along with me as this Excel guide will save you time and frustration.

Using the Format Numbers Tool

Converting a date to a number in Microsoft Excel using the Format Numbers tool is one of the quickest ways to get the job done. This method simply changes how Excel displays the date. The tool turns the date into its underlying serial number without affecting the actual value stored in the cell.

It’s straightforward, doesn’t require any formulas, and works well when you’re looking for a quick, one-time change in your sheet. What makes this method great is its speed and simplicity. It’s perfect when you’re working on a small dataset of the same row size, and you need the conversion right away.

Select the cell or range of cells containing the dates you want to convert. Make sure the selection is accurate because this change affects all selected cells instantly.

Go to the Home tab on the Excel ribbon and find the Number group. Look for the drop-down that might currently say Short Date or Long Date.

Click the drop-down and choose Number from the list of available formats.

Excel will immediately display the serial numbers that represent those dates.

If you want to remove decimal places, use the Decrease Decimal button in the same Number group. Click it until you see whole numbers only, which represent just the date portion.

Double-check the converted numbers to ensure they match your expectations. Remember, Excel counts dates as days since January 1, 1900, so the numbers might look large, but are correct.

If you ever want to switch back to dates, simply change the format back to Short Date or Long Date using the same drop-down. Excel will restore the original display instantly because the underlying value hasn’t changed.

Using the VALUE Function

The VALUE function in Excel is a quick and reliable way to turn a date into its numeric serial equivalent. You can use this method if you need an Excel function to convert dates to numbers.

This method is handy when you’re working with data that needs to stay intact while you view or use the numeric version elsewhere. Now, let me show you how to use this method in effortless steps:

Select an empty cell where you want the numeric result to appear. Keeping the original date separate ensures you don’t accidentally overwrite your source data.

Enter the following formula into the cell:

=VALUE(B2) 

When using this formula, don’t forget to change the cell reference to an actual cell from your own worksheet.

Hit Enter to calculate the cell.

If you need to convert multiple dates in a column, drag the fill handle (small square at the cell’s bottom-right corner) down to copy the formula.

This action applies the conversion formula and calculates all cells down the column automatically.

Suppose you want to keep only the numbers. Copy the converted column, select the destination cell, right-click, and choose Paste Values.

This step removes the formula and keeps only the raw numbers for further use.

Using the TEXT Function With a Numeric Format

Sometimes you might need to get the underlying numerical value of a date in text format in Excel. In such a scenario, you can use the TEXT function to convert dates to numbers. Let me walk you through the steps below to use this function, as well as the formula you must use:

Click a blank cell where you wish to store the converted number. Keeping the original date separate makes sure you don’t accidentally lose the source data.

Copy and paste the following formula into the destination cell:

=TEXT(B2,"0")

In the above formula, B2 is the source of the date value. So, change this cell reference according to your worksheet dataset.

Hit Enter to calculate the selected cell.

If there are more cells with date entries that you must convert to the corresponding numerical values, select the first cell.

Click on the tiny square in the bottom right corner of the cell. This is the fill handle. Keep it selected using the left mouse button.

Drag it down the column to cover all the data in the source column.

Excel will apply the formula automatically to all the selected cells.

You’ve successfully converted date formats to numbers using the TEXT function.

To use the output data as text, select it and press Ctrl + C to copy it.

Go to the destination cell or cell range and paste by pressing Ctrl + Alt + V.

The Paste Special dialog box will open. In that window, select the Values option below the Paste section.

Excel will paste the copied value as text strings.

Using the DATEVALUE Function

If you’ve got date entries, like 15-09-2019 or 15-September-2019 in text format, you can also use the DATEVALUE function to convert the text into the serial number of the date. Then, you can convert those serial numbers into actual date entries with the Short Date or Long Date number formatting functions. Let me walk you through the simple steps below:

Firstly, select a blank cell adjacent to the first date in the dataset. Type the following formula into the cell. The cell reference must be manually adjusted according to your own worksheet. Since my date entries are in the range B2:B8, I used B2 in the formula.

=DATEVALUE(B2)

Secondly, hit Enter to get the date serial number for the date entry in text format.

Thirdly, click on the fill handle of this cell. This is the tiny square box located in the bottom right corner of the cell.

Drag it down the column until the last cell where reference date entries exist in the left side column.

Excel will immediately apply the same DATEVALUE formula to these cells and convert the text dates into their corresponding numeric values.

You can now use this output in further formulas or export these as text-based values.

To convert the output to text, use the Paste Special function of Excel. I already explained the steps earlier in this guide.

Using Power Query

Using Power Query to convert a date to a number in Microsoft Excel is the best choice when you’re working with a large dataset that an ordinary Excel worksheet can’t handle efficiently.

Power Query creates a structured data transformation interface where you can transform dates into their numeric serials without stressing the workbook.

What makes it stand out is that it builds a reusable connection to your data source. Once set up, you don’t need to redo the steps every time. When the data changes, you simply refresh the query, and Excel updates everything automatically.

Also, this method allows you to systematically import data from third-party servers and systems in a visually guided manner.

So, if your data is in a database outside your PC, open Excel and go to the Data tab.

Now, click on the Get Data command and hover the cursor over a generic data connector, like From Database.

An overflow menu will open on the right side. Choose an option from the list, like From SQL Server Database.

Follow all the on-screen instructions to import the dataset into the Power Query Editor interface.

If your dataset is already in an Excel workbook, access the worksheet, select the dataset range, and click on the Data tab.

Select the From Table/Range command from the Get & Transform Data block.

The Create Table dialog box will show up. Click OK.

You’ll now see the dataset has been loaded into Power Query.

Select the column that contains the date entries.

Right-click and hover the cursor over the Change Type option.

An overflow menu will open on the right side. Select Whole Number from the context menu.

Power Query will convert the dates to date serial numbers.

Now, click on the File tab and choose the Close & Load To command.

The Import Data dialog box will open. Click on the Existing worksheet option and select a cell on the active worksheet where you wish to save the transformed data.

Click OK to confirm.

The converted dataset is now available in your Excel worksheet.

To refresh in the future when the source data changes, simply click on the newly imported data table.

Go to the Query tab in the Excel ribbon menu and click on the Refresh command in the Load block.

Click again on the Refresh button in the context menu.

Conclusions

If you’re here, congratulations! You’ve successfully learned and practised the 5 best methods to convert dates to numbers. Each of these methods solves specific scenarios and is equally important. Choose the method that fits your requirement.

If you’ve found this Excel tutorial useful, share it with your friends and colleagues. Don’t forget to comment below to share your opinion and feedback.