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

:

 

[Joe4]

If you are just entering the numbers in cells A1, B1, and C1, then your formula should just be:

=DATE(A1,B1,C1)

as the YEAR, MONTH, and DAY function are meant to be applied to existing date values, not standalone numbers like 2023.

 

[jblevins]

This return #NUM:

=DATE(Jul!F8,Jul!H1,Jul!F7)

The actual function that works that I want to replace the year in:

=DATE(YEAR(Jul!F8),MONTH(Jul!H1),DAY(Jul!F7))

 

[Kerryx]

Format column A as Special it seems to work

Book3
	A	B	C	D
1	2021	12	11	11/12/2021
2	2022	8	5	05/08/2022
3	2015	3	7	07/03/2015
4	2022	2	1	01/02/2022
5	1945	21	6	06/09/1946
6	1944	25	4	04/01/1946
7	1986	1	12	12/01/1986
8	3001	5	10	10/05/3001
9	3230	9	8	08/09/3230
Sheet1

Cell Formulas
Range	Formula
D1:D9	D1	=DATE(A1,B1,C1)

 

[Joe4]

OK, since you are entering in the year only, but the MONTH and DATE are coming from another date (which was not clear from your original post), you would just use:

=DATE(Jul!F8,MONTH(Jul!H1),DAY(Jul!F7))

Note: This all makes more sense when you understand how dates work in Excel.
Dates are really just numbers, with special date formats applied to them.
The number is just the number of days since 1/0/1900. You can see this easily by entering any date into Excel, and changing the format of that cell to "General".
This number is how Excel see dates.

So, if you enter the number 2023 in a cell, and then change it to a date format, you can see it represents the date: 7/15/1905.
That is why when you apply the YEAR function to that value, it returns 1905.
You do NOT want the year value from that date, you want to use that number, straight up, as the year. So you would not apply the YEAR function to it.

Make sense?

 

[Kerryx]

Sorry just spotted did not work years jumped up

 

[jblevins]

OK, since you are entering in the year only, but the MONTH and DATE are coming from another date (which was not clear from your original post), you would just use:

=DATE(Jul!F8,MONTH(Jul!H1),DAY(Jul!F7))

Note: This all makes more sense when you understand how dates work in Excel.
Dates are really just numbers, with special date formats applied to them.
The number is just the number of days since 1/0/1900. You can see this easily by entering any date into Excel, and changing the format of that cell to "General".
This number is how Excel see dates.

So, if you enter the number 2023 in a cell, and then change it to a date format, you can see it represents the date: 7/15/1905.
That is why when you apply the YEAR function to that value, it returns 1905.
You do NOT want the year value from that date, you want to use that number, straight up, as the year. So you would not apply the YEAR function to it.

Make sense?

The answer seems to be that I had the cell formatted wrong (changed it to General) where the year is entered and also had to remove the YEAR function.

 

[Joe4]

The answer seems to be that I had the cell formatted wrong (changed it to General) where the year is entered and also had to remove the YEAR function.

Yep, just like I said.

Does my explanation make sense?
If you understand it, it explains the reasoning for this.

 

[jblevins]

Removing YEAR was a help, thanks...

 

[Joe4]

Removing YEAR was a help, thanks...

But do you understand why you had to do this?

I took the time to try to explain why this is, and how dates work in Excel.
Did you read that part, and understand what I was explaining - how dates are stored in Excel?

Quote from previous reply:

Note: This all makes more sense when you understand how dates work in Excel.
Dates are really just numbers, with special date formats applied to them.
The number is just the number of days since 1/0/1900. You can see this easily by entering any date into Excel, and changing the format of that cell to "General".
This number is how Excel see dates.

So, if you enter the number 2023 in a cell, and then change it to a date format, you can see it represents the date: 7/15/1905.
That is why when you apply the YEAR function to that value, it returns 1905.
You do NOT want the year value from that date, you want to use that number, straight up, as the year. So you would not apply the YEAR function to it.

If there is any part of this you do not understand, please let me know.