Forum Discussion
formula error
Zaberz wrote:
In that cell (A3) I just put a date, 01-01-2024
I don't get the error pop up but the cell displays a Number error nowSo, we've solved the first problem, but now have another one. You definitely need to be using semi-colons instead of commas as the argument separator when writing formulas. =DATE(A$3;1;1) is now working properly; however, the #NUM! error is returned because the first argument is expecting a year from 0 to 9999, but you've used a date in cell A3.
The generic syntax for the DATE function is:
=DATE(year; month; day)You can input static values for each argument. For example:
=DATE(2024; 1; 1)Or you can use cell references for one or more arguments. For example, if A3 contained the year 2024:
=DATE(A3; 1; 1)However, since A3 contains the date 01/01/2024 in your example, you would need to use the YEAR function to extract the year from that date:
=DATE(YEAR(A3); 1; 1)Please note, Excel treats actual dates as sequential, numeric values from 1 (01/01/1900) to 2958465 (12/31/9999). The #NUM! error was returned because Excel reads 01/01/2024 as 45292, which exceeds the maximum value accepted for the year argument of the DATE function (9999).
Zaberz This is most likely caused by your Regional and Language settings (decimal symbol is set to a comma instead of a period). Try using semi-colons instead of commas as your argument separators. For example: =DATE(A$3;1;1)
- I don't get the error pop up but the cell displays a Number error now
Zaberz wrote:
In that cell (A3) I just put a date, 01-01-2024
I don't get the error pop up but the cell displays a Number error nowSo, we've solved the first problem, but now have another one. You definitely need to be using semi-colons instead of commas as the argument separator when writing formulas. =DATE(A$3;1;1) is now working properly; however, the #NUM! error is returned because the first argument is expecting a year from 0 to 9999, but you've used a date in cell A3.
The generic syntax for the DATE function is:
=DATE(year; month; day)You can input static values for each argument. For example:
=DATE(2024; 1; 1)Or you can use cell references for one or more arguments. For example, if A3 contained the year 2024:
=DATE(A3; 1; 1)However, since A3 contains the date 01/01/2024 in your example, you would need to use the YEAR function to extract the year from that date:
=DATE(YEAR(A3); 1; 1)Please note, Excel treats actual dates as sequential, numeric values from 1 (01/01/1900) to 2958465 (12/31/9999). The #NUM! error was returned because Excel reads 01/01/2024 as 45292, which exceeds the maximum value accepted for the year argument of the DATE function (9999).
