Enter year alone in cell.

[jblevins]

I want to enter the year only in a cell and use it in the DATE function, but it always returns 1905.

Enter 2023 in A1 as text
DATE(YEAR(A1),MONTH(B1),DAY(C1))

:

 

[jblevins]

The workbook that I am working on is a little complicated with several defined names and is capable of handing a yearly calendar and storing information entered into dates for any year or month. The issue was displaying the information for the year that is entered, basically it is for appointments, my problem is that I will not leave it alone, I keep making changes to it.

 

[Kerryx]

@Joe4 So if you just enter the value as a text string and then apply the YEAR function to it , should that then work as below or am i missing something in his question.

Book1
	A	B	C	D
1	Year	Month	Day	Date
2	1990	3	1	01/03/1990
3	1910	4	2	02/04/1910
4	2014	7	3	03/07/2014
5	2018	8	4	04/08/2018
6	2022	10	5	05/10/2022
7	2025	4	6	06/04/2025
8	2030	6	7	07/06/2030
9	2045	9	8	08/09/2045
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=DATE(A2,B2,C2)

 

[jblevins]

Yes I understand how Excel stores and sees dates, but the issue I had was that I had the cell formatted wrong causing the error.

 

[Joe4]

@Joe4 So if you just enter the value as a text string and then apply the YEAR function to it , should that then work as below or am i missing something in his question.

Book1
	A	B	C	D
1	Year	Month	Day	Date
2	1990	3	1	01/03/1990
3	1910	4	2	02/04/1910
4	2014	7	3	03/07/2014
5	2018	8	4	04/08/2018
6	2022	10	5	05/10/2022
7	2025	4	6	06/04/2025
8	2030	6	7	07/06/2030
9	2045	9	8	08/09/2045
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=DATE(A2,B2,C2)

In the example you posted, you are NOT apply the YEAR function to anything (look at your formulas, nowhere are you using the YEAR function).
That is what I already showed in my first reply, before I realized that what you assumed/posted (which is what I also initially assumed) is NOT actually the data structure that the user has (they are pulling MONTH and DAY from an existing date). So they are pulling year from a number/text entry, and the rest from an existing date.

 

[Joe4]

Yes I understand how Excel stores and sees dates, but the issue I had was that I had the cell formatted wrong causing the error.

Note that if you have a number entered as Text that you want to treat as a number, one simple way to convert it is to simply add zero to it to coerce it to become a number, i.e.
Jul!F8+0
If it already is a number, adding zero to it will not hurt it/change its value.

 

[jblevins]

I understand it all, I also understand that serial of the date starts with 1900. I appreciate the help, have a great day and God bless.