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 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 now
So, 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).
