Arrow left
Back to product guides
Google Sheets Formulas

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 to use the DATE function in Google Sheets with examples

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")

How do I manipulate or automatically calculate dates in Google Sheets?

See the following formulas related to dates and their descriptions to check if they provide the computations you want to learn.

Formulas related to dates

How to Use EDATE Function in Google Sheets - understand a date before/after a specific number of days.

DAYS Function in Google Sheets: Explained - calculate the number of days between the two dates.

WORKDAY Function in Google Sheets: Explained - show the ending date based on inputs of specific starting date and the number of working days, excluding holidays.

NETWORKDAYS Function in Google Sheets: Explained - calculate the number of working days between two dates, excluding holidays.

EOMONTH Function in Google Sheets: Explained - understand an end date of a month.

DATEVALUE Function in Google Sheets: Explained - convert a date into a serial number.

YEAR Function in Google Sheets: Explained - extract the year from a given date.

MONTH Function in Google Sheets: Explained - extract the month from a given date.

DAY Function in Google Sheets: Explained - extract the day from a given date.

Formulas related to time

How to Insert Current Time in Google Sheets - NOW function

Analyze your live financial data in a snap

Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you automate manual workflows and keep numbers updated in real time. You can access more than fifty financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711