How to use the DATE function to combine columns with date parts in Excel

Sometimes, you may have a spreadsheet that contains date information in separate columns, such as year, month, and day. For example, you may have a table like this:

In this case, you may want to combine these columns into a single column that contains the full date in a standard format, such as 01/15/2024. This can make it easier to perform calculations, filter, sort, or format the dates.

One way to do this is to use the DATE function in Excel. The DATE function takes three arguments: year, month, and day, and returns a serial number that represents the date. You can then format the serial number as a date using the Format Cells dialog box or the Number group on the Home tab.

Syntax

The syntax of the DATE function is:

=DATE(year, month, day)

• year is a number that represents the year of the date. It can be a four-digit or a two-digit number. If it is a two-digit number, Excel assumes that the year is between 1900 and 1999. For example, 24 is interpreted as 1924, while 2024 is interpreted as 2024.
• month is a number that represents the month of the date. It can be a positive or a negative number. If it is a positive number, it is the actual month. For example, 1 is January, 2 is February, and so on. If it is a negative number, it subtracts the number of months from the start of the year. For example, -1 is December of the previous year, -2 is November of the previous year, and so on.
• day is a number that represents the day of the date. It can be a positive or a negative number. If it is a positive number, it is the actual day. For example, 15 is the 15th day of the month. If it is a negative number, it subtracts the number of days from the end of the month. For example, -1 is the last day of the month, -2 is the second to last day of the month, and so on.

Example

Let’s see how to use the DATE function to combine the columns with date parts in the table above. We can use the following formula in cell D2 and copy it down to the rest of the column:

=DATE(A2, B2, C2)

This formula takes the values from columns A, B, and C, and returns the serial number that corresponds to the date. The result will look like this:

To format the serial numbers as dates, we can select column D, right-click, and choose Format Cells. In the Number tab, we can select Date from the Category list, and choose the desired format from the Type list. For example, we can choose the format that shows the date as mm/dd/yyyy. The result will look like this:

Other approaches

There are other ways to combine columns with date parts in Excel, such as using the TEXT function, the CONCATENATE function, or the ampersand (&) operator. However, these methods may not return a valid date value that can be used for calculations, filtering, sorting, or formatting. Therefore, the DATE function is the preferred method for combining columns with date parts in Excel.