DATE Function in Google Sheets: Explained

In this article, you will learn how to use the DATE formula in Google Sheets.

‍

What is the DATE formula in Google Sheets?

The DATE formula in Google Sheets is a function that allows you to build a date by specifying the year, month, and day as arguments. This formula is beneficial when you want to ensure that your inputs for other formulas are considered dates by Google Sheets.

‍

How to use the DATE function in Google Sheets?

To use the DATE function in Google Sheets, follow these steps:

1. Click on the cell where you want to enter the date.
2. Type the “=DATE”, followed by an open parenthesis (.
3. Enter the year, month, and day arguments, separated by commas.
4. Close the parenthesis and press the “Enter” key.

The general syntax of the DATE formula is as follows:

year:  The year part of a date.

month: The month part of a date.

day: The day part of a date.

‍

Note 1: The argument inputs should be numeric values. Otherwise, the formula returns “#VALUE!”.

Note 2: The DATE function automatically adjusts the input values when they are out of the general month or day ranges, such as the 13th month or the 33rd day. For example, DATE(2022, 13, 1) returns a date of 1/1/2023. Another example is that, if you insert the DATE(2022 ,1 ,33), it returns a date of 2/2/2022

Note 3: The DATE formula rounds down decimal values input into the function; e.g., a month of 1.9 will be considered 1.

Note 4: Google Sheets uses the 1900 date system. The first date is 1 January 1900. Thus,  if you input a numeric value between 0 and 1899 as a parameter for the DATE function, Google Sheets adds that value to 1900 to calculate the year. For instance, DATE(111,1,1) will provide a date of 1/1/2011. For years smaller than 0 or greater than 10,000, Google Sheets will show the “#NUM!” error value.

‍

For example, to create a date for January 1, 2021, you could use the following formula:

=DATE(2021, 1, 1)

This would return the date 1/1/2021 in the cell where the formula is entered.

You can also use cell references in the arguments of the DATE formula. For example, if you have the year in cell A1, the month in cell B1, and the day in cell C1, you could use the following formula:

=DATE(A1, B1, C1)

This would return a date based on the values in cells A1, B1, and C1.

You can see examples in the following screenshot. The first example shows the formula containing input parameters manually. The others included arguments input by cell references.

‍

How do I set dd/mm/yyyy in Google Sheets?

To set the date format to dd/mm/yyyy in Google Sheets, follow these steps:

1. Select the cells that you want to format.
2. Navigate to the "Format" tab → “Number”.
3. Click on the "Date" button in the menu to apply the formatting.

You can also use the TEXT function to format the date as dd/mm/yyyy. For example, the following formula would return the date "1/1/2021" in the dd/mm/yyyy format:

=TEXT(DATE(2021, 1, 1), "dd/mm/yyyy")