YEAR Function Returning #VALUE error

[Craig Peter DG]

Hi, have come to the board because I cant find a solution anywhere else.

Having probs with a YEAR function, keeps returning #VALUE error even though the cell is referencing a Date formatted cell. The date formatted cell is in United States date format, thus 12/15/2020 (15 December 2020). The date cell is itself a formula extracted from another sheet within the workbook and i have triple checked that the date returned here is correctly formatted as a date. However, i now want to extract the YEAR from that date in the adjacent column but i keep getting a VALUE error. Solutions please? Here is a snapshot of my excel table.

VAERS ID#	ID Source this Wbook	Date Reported to VAERS	YEAR
902440​	2020CV19EventsPHARMA	12/15/2020	#VALUE!

 

[jtakw]

Hi,

You may have formatted the cell as Date; however the cell value may Still be Text, double check by using
=ISNUMBER(cell with the date)

If it's FALSE, since the date cell value is populated by formula, check your formula and/or post it.

 

[AlphaFrog]

What is your formula that extracts the date from another sheet?

It the result of that extract formula returns a text-date, then try this tor get the year.

=YEAR(DATEVALUE(C2))

 

[Phuoc]

Try this:

=IFERROR(YEAR(--C2),--RIGHT(C2,4))

 

[Craig Peter DG]

What is your formula that extracts the date from another sheet?

It the result of that extract formula returns a text-date, then try this tor get the year.

=YEAR(DATEVALUE(C2))

Thanks Alpha. So I acquire the date first, by using a VLOOKUP to reference another worksheet in the same workbook, =VLOOKUP(A6,'2020AllEventsGENERAL'!A:AI,2,FALSE).
In the example below, A6 is the number 902440. So I look that up using VLOOKUP. The result gives me Date Reported to VAERS result 12/15/2020 (United States Format). I make sure that the Date Reported to VAERS column is correctly formatted as Date. Then I try and use the YEAR formula (far right column below) and that formula references the Date Reported to VAERS date to attempt to give me the year, but i get VALUE error. Hope this helps. Thanks

VAERS ID#	ID Source this Wbook	Date Reported to VAERS	Year (formula)
902440​	2020CV19EventsPHARMA	12/15/2020	#VALUE!
902446​	2020CV19EventsPHARMA	12/15/2020	#VALUE!
902464​	2020CV19EventsPHARMA	12/15/2020	#VALUE!

 

[Craig Peter DG]

Hi,

You may have formatted the cell as Date; however the cell value may Still be Text, double check by using
=ISNUMBER(cell with the date)

If it's FALSE, since the date cell value is populated by formula, check your formula and/or post it.

Thanks Alpha. So I acquire the date first, by using a VLOOKUP to reference another worksheet in the same workbook, =VLOOKUP(A6,'2020AllEventsGENERAL'!A:AI,2,FALSE).
In the example below, A6 is the number 902440. So I look that up using VLOOKUP. The result gives me Date Reported to VAERS result 12/15/2020 (United States Format). I make sure that the Date Reported to VAERS column is correctly formatted as Date. Then I try and use the YEAR formula (far right column below) and that formula references the Date Reported to VAERS date to attempt to give me the year, but i get VALUE error. Hope this helps. Thanks

VAERS ID#	ID Source this Wbook	Date Reported to VAERS	Year (formula)
902440​	2020CV19EventsPHARMA	12/15/2020	#VALUE!
902446​	2020CV19EventsPHARMA	12/15/2020	#VALUE!
902464​	2020CV19EventsPHARMA	12/15/2020	#VALUE!

Here is a snapshot of the errors and now with the FALSE IS NUMBER result................................

 

[Craig Peter DG]

Hi,

You may have formatted the cell as Date; however the cell value may Still be Text, double check by using
=ISNUMBER(cell with the date)

If it's FALSE, since the date cell value is populated by formula, check your formula and/or post it.

Hi jtakw, I get a FALSE answer when I use the ISNUMBER formula against the reference date?????????

 

[Craig Peter DG]

Hi,

You may have formatted the cell as Date; however the cell value may Still be Text, double check by using
=ISNUMBER(cell with the date)

If it's FALSE, since the date cell value is populated by formula, check your formula and/or post it.

I have tried several suggestions folks, still coming up with VALUE ERROR for the Year.

 

[Craig Peter DG]

What is your formula that extracts the date from another sheet?

It the result of that extract formula returns a text-date, then try this tor get the year.

=YEAR(DATEVALUE(C2))

I have tried several suggestions folks, still coming up with VALUE ERROR for the Year.

 

[Craig Peter DG]

Thanks Alpha. So I acquire the date first, by using a VLOOKUP to reference another worksheet in the same workbook, =VLOOKUP(A6,'2020AllEventsGENERAL'!A:AI,2,FALSE).
In the example below, A6 is the number 902440. So I look that up using VLOOKUP. The result gives me Date Reported to VAERS result 12/15/2020 (United States Format). I make sure that the Date Reported to VAERS column is correctly formatted as Date. Then I try and use the YEAR formula (far right column below) and that formula references the Date Reported to VAERS date to attempt to give me the year, but i get VALUE error. Hope this helps. Thanks

VAERS ID#	ID Source this Wbook	Date Reported to VAERS	Year (formula)
902440​	2020CV19EventsPHARMA	12/15/2020	#VALUE!
902446​	2020CV19EventsPHARMA	12/15/2020	#VALUE!
902464​	2020CV19EventsPHARMA	12/15/2020	#VALUE!

I have tried several suggestions folks, still coming up with VALUE ERROR for the Year.