How to Convert Date to Number in Excel

Convert the Date into a Number

As I said at the start, Excel stores dates as serial numbers, and you can convert a date into a serial number simply by changing its formatting.

1. First, select the cell where you want to convert the date into a number.
2. Next, open the Format Cells dialog box by using the keyboard shortcut Ctrl + 1 (Command + 1 on a Mac).
3. Now, from the Category list, select Number.
4. In the end, set Decimal places to 0 and click OK.

Once you change the formatting, you will get the number instead of the date, just like in the following example.

The number you get is 44562. Excel counts days from 1-Jan-1900, which is 1, so 1-Jan-2022 is the 44562nd day in that count.

There’s a quicker way too. Select the cell, go to Home > Number Format (the drop-down in the Number group) and select General. Or use the keyboard shortcut Ctrl + Shift + ~ to apply the General format. With General, you don’t need to set decimal places, and a date with a time shows the time as a decimal.

Keep in mind that formatting only changes how the value looks. The value in the cell is already a number, so nothing changes in your formulas. This also means that formatting doesn’t work on a date stored as text. For that, you need the method below.

Convert Text Date into a Number

There could be a situation where you have a date in a cell that is stored as text, and now you want to convert it into a number. In this case, you need to use the DATEVALUE function, which converts a date stored as text into a date serial number.

1. Enter “=DATEVALUE” in a cell.
2. Then enter an opening parenthesis.
3. Refer to the cell where you have the date.
4. Now, enter a closing parenthesis and press Enter.

Here’s the formula with the text date in cell A1:

=DATEVALUE(A1)

If the result shows as a date, change the cell format to General. DATEVALUE returns a serial number, but Excel can show it with a date format.

There are a few things you need to know about DATEVALUE:

• Text only: It works only on dates stored as text. If you refer to a real date, it returns the #VALUE! error.
• Date range: The text date must be between 1-Jan-1900 and 31-Dec-9999. Text that Excel can’t read as a date (like “abc” or a cell with only a space) also returns #VALUE!.
• Your date settings: Excel reads the text using your computer’s date settings, so “01/02/2022” can be 2-Jan or 1-Feb depending on the system.
• No year: If the text has no year (like “5-Jul”), DATEVALUE uses the current year from your computer’s clock.
• Time is ignored: If the text has a time, DATEVALUE skips it and returns only the date part.

You can also use the VALUE function, or simply multiply the text date by 1. Both convert a text date into a number, and unlike DATEVALUE, they keep the time if the text has one:

=VALUE(A1)
=A1*1

If the text date has extra spaces at the start or end, wrap it in TRIM before converting it:

=DATEVALUE(TRIM(A1))

Convert a Date into a Number with a Formula

If you want to keep the original date as it is and get the number in another cell, you can use a simple formula. With the date in cell A1, multiply it by 1:

=A1*1

Excel often applies the date format of A1 to the result, so you’ll still see a date. Just change the format of the result cell to General, and you’ll get the serial number. The N function works the same way and returns the serial number of a date:

=N(A1)

Convert a Date with Time into a Number

Excel stores time as a fraction of a day. So, when a cell has a date and a time, its serial number has decimals. For example, 1-Jan-2022 2:30 PM is 44562.6041666667. When you use the General format, you get the full number with the decimals.

If you only want the date part as a whole number, use the INT function, which removes the decimal part:

=INT(A1)

Don’t use the Number format with zero decimals for this. It only hides the decimals, and the value in the cell still has the time.

Convert a Date into a Number in YYYYMMDD Format

Sometimes you don’t need the serial number, but a number like 20220101 for 1-Jan-2022. Many systems and file names use this format. For this, you can use the TEXT function to get the date as yyyymmdd and then the double minus to turn that text into a number:

=--TEXT(A1,"yyyymmdd")

You can change the format code to get other orders, like “ddmmyyyy” or “mmddyyyy”. But a number in ddmmyyyy or mmddyyyy loses the leading zero for days or months below 10 (01012022 becomes 1012022), so yyyymmdd is the safest choice.

If you only want to show the date this way and keep the serial number in the cell, open Format Cells (Ctrl + 1), select Custom, and enter yyyymmdd in the Type box.

Check if a Date Is a Real Date or Text

Before you convert, it helps to know what you have in the cell. A real date is a number, and a text date is text. By default, Excel aligns real dates to the right and text dates to the left, but the alignment can be changed, so a formula is the better test:

=ISNUMBER(A1)
=ISTEXT(A1)

ISNUMBER returns TRUE for a real date, and ISTEXT returns TRUE for a text date. A green triangle in the corner of the cell is another sign of a date stored as text.

Convert Many Text Dates at Once

When you have a whole column of text dates, you don’t need a formula for each one. Select the column, go to Data > Text to Columns, click Next twice, select Date under Column data format, choose the order of the date (like DMY or MDY), and click Finish. Excel converts the text into real dates in place, and then you can apply the General format to get the numbers.

To learn more ways to fix text dates, check out Convert Text to Date in Excel.

Date Numbers on a Mac and in Google Sheets

Excel for Windows and Excel for Mac (2011 and later) both use the 1900 date system by default, so you get the same numbers. But a workbook can use the 1904 date system, which was the default in older versions of Excel for Mac. In that system, the serial number of a date is 1,462 lower, so 1-Jan-2022 is 43100. You can check this setting in File > Options > Advanced, under When calculating this workbook, with the option Use 1904 date system.

In Google Sheets, all of the above methods work too. To change the format, go to Format > Number > Number, and for modern dates you get the same serial numbers as in Excel.

Which Method to Use

• Real date, same cell: Change the format to General or Number.
• Real date, another cell: Multiply the date by 1 or use N, and format the result as General.
• Date with time, date part only: Use INT.
• Text date: Use DATEVALUE, or VALUE when you want to keep the time.
• Many text dates: Use Text to Columns.
• A number like 20220101: Use TEXT with yyyymmdd.

Frequently Asked Questions

=TODAY()