5 Ways To Convert Excel Date Number To Date

Excel date numbers can be a bit tricky to work with, especially when you need to convert them to a readable date format. In this article, we will explore five ways to convert Excel date numbers to dates.

Working with dates in Excel can be challenging, especially when dealing with date numbers. These numbers represent the number of days since January 1, 1900, which is the default date system used by Excel. However, these numbers are not very informative, and it's often necessary to convert them to a more readable date format.

Fortunately, there are several ways to convert Excel date numbers to dates, and we will cover five of them in this article.

Understanding Excel Date Numbers

Before we dive into the methods, it's essential to understand how Excel date numbers work. As mentioned earlier, these numbers represent the number of days since January 1, 1900. For example, the date number 1 corresponds to January 1, 1900, while the date number 2 corresponds to January 2, 1900, and so on.

Method 1: Using the TEXT Function

One of the easiest ways to convert an Excel date number to a date is by using the TEXT function. This function converts a value to text in a specified format.

Here's an example:

=TEXT(A1,"dd-mmm-yyyy")

Assuming the date number is in cell A1, this formula will convert it to a date in the format dd-mmm-yyyy (e.g., 12-Jan-2022).

Example

Date Number	Formula	Result
44562	=TEXT(A1,"dd-mmm-yyyy")	12-Jan-2022

Method 2: Using the DATE Function

Another way to convert an Excel date number to a date is by using the DATE function. This function returns a date value from a given year, month, and day.

Here's an example:

=DATE(2022,1,12)

This formula will return the date January 12, 2022.

However, if you want to convert a date number to a date, you can use the following formula:

=DATE(1900,1,A1) + (A1-1)

Assuming the date number is in cell A1, this formula will convert it to a date.

Example

Date Number	Formula	Result
44562	=DATE(1900,1,A1) + (A1-1)	12-Jan-2022

Method 3: Using the INT and MOD Functions

This method involves using the INT and MOD functions to extract the year, month, and day from the date number.

Here's an example:

=DATE(INT((A1-1)/365.25)+1900,INT(MOD((A1-1),365.25)/30.4375)+1,MOD(MOD((A1-1),365.25),30.4375)+1)

Assuming the date number is in cell A1, this formula will convert it to a date.

Example

Date Number	Formula	Result
44562	=DATE(INT((A1-1)/365.25)+1900,INT(MOD((A1-1),365.25)/30.4375)+1,MOD(MOD((A1-1),365.25),30.4375)+1)	12-Jan-2022

Method 4: Using the WORKDAY Function

The WORKDAY function returns a date a specified number of workdays before or after a given date.

Here's an example:

=WORKDAY(1900,1,A1-1)

Assuming the date number is in cell A1, this formula will convert it to a date.

Example

Date Number	Formula	Result
44562	=WORKDAY(1900,1,A1-1)	12-Jan-2022

Method 5: Using VBA

If you prefer using VBA, you can use the following code to convert an Excel date number to a date:

Function DateFromNumber(dateNumber As Long) As Date DateFromNumber = DateAdd("d", dateNumber - 1, "1900-01-01") End Function

You can then use this function in your worksheet as follows:

=DateFromNumber(A1)

Assuming the date number is in cell A1, this formula will convert it to a date.

Example

Date Number	Formula	Result
44562	=DateFromNumber(A1)	12-Jan-2022

Gallery of Excel Date Conversions

Excel Date Conversion Examples

We hope this article has helped you learn five ways to convert Excel date numbers to dates. Whether you prefer using formulas or VBA, there's a method that suits your needs. If you have any questions or need further assistance, feel free to ask in the comments section below.

Don't forget to share this article with your friends and colleagues who might find it useful. Happy Excel-ing!