SOLVED

# formula error

Copper Contributor

# 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?

6 Replies

# Re: formula error

What is in cell A\$3?

# Re: formula error

@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)

# Re: formula error

In that cell I just put a date, 01-01-2024

# Re: formula error

I don't get the error pop up but the cell displays a Number error now
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: 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 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).

# Re: formula error

I'm trying to learn excel but ...

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.

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: 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 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).