keep in mind javascript is required for full website functionality.
Welcome back to our regular blog of Excel functions from A to Z. Today we look at the DATE function.
The DATE function
This function returns the sequential serial number that represents a particular date. Basically, this was a method to avoid the 'feared Year 2000 error' which referred to potential division by zero errors.
The DATE The function uses the following syntax to operate:
The DATE The function has the following arguments:
- year: is required. The value of the year argument can include from one to four digits. Excel interprets the year argument according to the date system used by your computer. By default, Microsoft Excel for Windows uses the date system 1900, which means that the first date is 1 of January of 1900. It is better to use four digits for the year argument to avoid undesired results. For instance, “18” could mean “1918” O “2018”; four-digit years prevent that confusion.
- And year is between 0 (zero) Y 1899 (inclusive), Excel adds that value to 1900 para calcular el año. For instance, DATE (108,1,2) return the 2 of January of 2008 (1900 + 108)
- And year is between 1900 Y 9999 (inclusive), Excel usa ese valor como año. For instance, DATE (2008,1,2) regresa el 2 of January of 2008
- And year is less than 0 o es 10000 o mayor, Excel devuelve el #ON ONE! error value
- my: esto también es obligatorio y debe ser un número entero positivo o negativo que represente el mes del año del 1 al 12 (enero a diciembre)
- And my is greater than 12, my agrega ese número de meses al primer mes del año especificado. For instance, DATE (2018,14,2) devuelve el número de serie que representa el 2 February 2019
- And my is less than 1, my resta la magnitud de ese número de meses, plus 1, del primer mes del año especificado. For instance, DATE (2018, -3,2) devuelve el número de serie que representa el 2 September 2017
- day: again, this is obligatory. Este es un número entero positivo o negativo que representa el día del mes del 1 al 31.
- And day es mayor que el número de días del mes especificado, day agrega ese número de días al primer día del mes. For instance, DATE (2018,1,35) devuelve el número de serie que representa el 4 February 2018
- And day is less than 1, day resta la magnitud de ese número de días, más uno, del primer día del mes especificado. For instance, DATE (2018,1, -15) devuelve el número de serie que representa el 16 from December to 2017.
It should also be noted that:
- Excel stores dates as sequential serial numbers so that they can be used in calculations. The 1 of January of 1900 is the serial number 1 and the 6 July 2009 is the serial number 40000 because it is 39,999 days after 1 of January of 1900.
- you will need to change the format of the number (CTRL + 1, 'Cell format') to display a suitable date
- The 29 February 1900 recognized as day 60 in the date system 1900. This date does not exist (years ending in “00” must be divisible by 400 to be a leap year), but this bug has been perpetuated for consistency / compatible con Lotus 1-2-3.
Please, see my example below:
Soon we will continue with our functions from A to Z of Excel. Keep checking: there is a new blog post every business day.
You can find a full page of feature articles here.
