In this article, you will learn how to use 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.
To use the DATE function in Google Sheets, follow these steps:
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:
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:
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.
To set the date format to dd/mm/yyyy in Google Sheets, follow these steps:
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:
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
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.
Learn how to do this step-by-step in the video below 👇