How to Convert Date as a Number in Excel

Understand Excel’s Date and Number Systems

Excel stores each date as a unique serial number. This allows Excel to perform calculations using dates easily. For example, January 1, 1900, is stored as serial number 1. The serial number increases by one for each new day.

When you see a date in a cell, Excel may actually be displaying a number using a Date format. Changing the cell format to General or Number shows the underlying serial number. You can also use date serial numbers in formulas, reports, and data analysis.

 

How to Convert Date as a Number

Format tool

STEP 1: Click on the cell or cells containing the dates that you want to convert to number format.

STEP 2: Go to the Ribbon and click on the “Home” tab. Locate the “Number” group.

STEP 3: Select the “Number” from the dropdown.

The TEXT Function

STEP 1: Prepare your data with dates to be converted.

STEP 2: Use the TEXT function in column B. Insert the formula “=TEXT(A2,”0″)” in cell B2 to convert the date in A2 to a number.

STEP 3: Drag the output in cell A2 to all the rows below to emulate the same outcome

DATEVALUE Function

The DATEVALUE function specializes in turning text-based dates into serial numbers that Excel can comprehend.

Format Cells Dialog Box

STEP 1: Select the dates.

STEP 2: Right-click, and choose ‘Format Cells’.

STEP 3: Under the ‘Number’ tab, click ‘General’ or ‘Number’.

Click OK

 

Common Pitfalls

Sometimes Excel’s default date format plays hard to get and doesn’t jive with your data’s aesthetic. Fear not—taming this unruly beast is within reach. Step into the Control Panel on your computer and navigate to ‘Clock and Region’ and select ‘Region’.

Here, you can court and coax Excel’s underlying preferences to your liking, be it to align with American month-day-year or the day-month-year flourish favored elsewhere.

 

FAQs

How to convert date into serial number in Excel?

To convert a date into a serial number,

• Select the date cell
• Press ‘Ctrl+1’ to open the ‘Format Cells’ dialog
• Under Number tab, choose General.

Why are converted numbers still shown as dates?

If converted numbers appear as dates, it is because the cell format is still set to ‘Date’. You can change it by following these steps:

• Press Ctrl + 1
• In the dialog box, go to Number tab
• Select General or Number

Can I convert a range of dates to numbers all at once?

Absolutely! You can convert an entire range of dates to numbers in one fell swoop. Just highlight the range, right-click, choose ‘Format Cells’, switch to ‘General’ or ‘Number’, and click OK. Excel will perform the mass conversion instantly.

How to convert date format in Excel?

To convert date format in Excel,

• Select the date cells
• Press ‘Ctrl+1’ to bring up the ‘Format Cells’ dialog
• Under the ‘Number’ tab, click ‘Date
• Choose your preferred format
• Click OK

How to convert date into no of days in Excel?

To convert a date into the number of days, use the DATEDIF function. Enter “=DATEDIF(start_date, end_date, “d”)” in a cell, replacing start_date and end_box with your specific dates. Excel will then display the days between the two dates.