Forum Discussion
formula error
I'm trying to learn excel but I've run into a big issue. When I write a relatively simple formula excel gives me an error message. As a refrence, I wrote this and it gave the same error message: =DATE(A$3,1,1)
How can I fix this?
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).
- What is in cell A$3?
- In that cell I just put a date, 01-01-2024
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).
I'm trying to learn excel but ...
What resources are you using to help you in this learning process? There are a number of good books on the market. Also loads of YouTube links.
I'd also recommend Exceljet.net , accessible through that link. It gives lots of great examples of how to use each function. Here, for example, is the guidance on the DATE function.
