DATE function - Zoho Sheet - Help Guide

DATE

Returns the date, given the year, month, and day of the month.

 

Syntax

 

DATE(year; month; day)

year: The year to display in the result. Must be an integer between 1583 and 9956, or between 0 and 99. E.g. 2020

month: The month to display in the result. Must be an integer. If month value is:

0 -  It subtracts the first month from the year. E.g. DATE(2017,0,2) gives December 2, 2016

Greater than 12 - It adds number of extra months to the given year. E.g. DATE(2016,13,2) gives January 2, 2017

Less than 0 - It subtracts number of months from the given year. E.g. DATE(2017,-2,1) gives October 1, 2016

day: The day to display in the result. Must be an integer. When day is:

Greater than days in the specified month, it adds number of extra days to the next month. E.g. DATE(2017,1,32) gives February 1, 2017.

0 - Subtracts the current day in the given month and year. E.g. DATE(2017,1,0) gives December 31, 2016

Less than 0 - Subtracts number of days from the given month, i.e. DATE(2017,1,-2) gives December 29, 2016

 

Examples

 

Formula

Result

=DATE(2020;1;1)

1/1/20

=DATE(2020;1;0)

12/31/19

=DATE(2020;15;1)

3/1/21


Get a hands-on experience of the function in the embedded range below. 



Possible Errors

 

Errors

Meaning

#N/A!

  • The function could not find the value it was asked to look for. Commonly occurs in VLOOKUP and similar functions.

#NAME!

  • The function name is incorrect or invalid.

  • The given defined name (if any) is invalid.

  • There is a typo in the defined name used in the function.

  • Double quotes are missing for text values in the function.

  • Colon missing in a cell range reference.

#VALUE!

  • The given argument's data type is incorrect or invalid.
    For example, the function =DATE(A1:A3) will return a #VALUE error when a text value is present in the range.

#REF!

  • The given cell reference is incorrect or invalid. This error may occur when a cell/range has been deleted, moved, or pasted over.

#NUM!

  • The given year is less than 0.

 

Similar Functions