8.5 C
London
Monday, December 30, 2024
HomeSoftware TutorialsExcelHow to Convert Date to Number in Excel (3 Examples)

How to Convert Date to Number in Excel (3 Examples)

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

This tutorial explains how to convert a date to a number in three different scenarios:

1. Convert One Date to Number

2. Convert Several Dates to Numbers

3. Convert Date to Number of Days Since Another Date

Let’s jump in!

Example 1: Convert One Date to Number

Suppose we would like to convert the date “2/10/2022” to a number in Excel.

We can use the DATEVALUE function in Excel to do so:

=DATEVALUE("2/10/2022")

By default, this function calculates the number of days between a given date and 1/1/1900.

The following screenshot shows how to use this function in practice:

This tells us that there is a difference of 44,602 days between 2/10/2022 and 1/1/1900.

Example 2: Convert Several Dates to Numbers

Suppose we have the following list of dates in Excel:

To convert each of these dates to a number, we can highlight the range of cells that contain the dates, then click the Number format dropdown menu on the Home tab and choose Number:

This will automatically convert each date to a number that represents the number of days between each date and 1/1/1900:

Example 3: Convert Date to Number of Days Since Another Date

We can use the following formula to convert a date to a number of days since another date:

=DATEDIF(B2, A2, "d")

This particular formula calculates the number of days between the date in cell B2 and the date in cell A2.

The following screenshot shows how to use the DATEDIF formula to calculate the number of days between the dates in column A and 1/1/2022:

Here’s how to interpret the values in column B:

  • There are 3 days between 1/1/2022 and 1/4/2022.
  • There are 8 days between 1/1/2022 and 1/9/2022.
  • There are 14 days between 1/1/2022 and 1/15/2022.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to AutoFill Dates in Excel
How to Use COUNTIFS with a Date Range in Excel
How to Calculate Average If Between Two Dates in Excel

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories