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!

 

[Craig Peter DG]

T

Try this:

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

The IFERROR formula seemed to work Phuoc. When I apply that formula to my Date reference, it returns 2020. I will now try and use the formula in other cells with different Years and Dates. Can you please explain what this formula is actually doing here????? I just dont get why this worked... Thanks.

 

[AhoyNC]

Just a guess, but could there be a space before or after the date.
Maybe try:
=YEAR(TRIM(C8))

 

[Craig Peter DG]

I think I have a solution folks, thanks to Phuoc. Thankyou. Still don't understand why my YEAR column simply would not recognise the date as a date and thus return the Year.

The IFERROR formula seemed to work Phuoc. When I apply that formula to my Date reference, it returns 2020. I will now try and use the formula in other cells with different Years and Dates. Can you please explain what this formula is actually doing here????? I just dont get why this worked... Thanks.

 

[Craig Peter DG]

tha

Just a guess, but could there be a space before or after the date.
Maybe try:
=YEAR(TRIM(C8thank

Just a guess, but could there be a space before or after the date.
Maybe try:
=YEAR(TRIM(C8))

No, i checked that. Thanks.

 

[Craig Peter DG]

Thankyou so much for everyone's help with this problem. Upon further investigation, after I noticed a secondary formatting issue in the returned date column, I went back and checked the date formatting of the SOURCE worksheet. NB:the source worksheet was a data dump from an external source, not my data. Well when i checked the dates in that data dump, some of them were in correct United States format (MDY) and some of them were in English format (DMY). So in other words, it was the SOURCE data that had inconsistent date formatting.

What I did to correct this problem, was actually FIX the date formatting in the source data by using the Text to Columns function for the date column, Delimited, No Separations, Date changed to MDY in the entire column. After I did that fix to the source data, and I went back and checked that the formula result for the date in my data was now correctly and uniformly formatted, the YEAR function finally worked by itself.

This was a huge learning curve for me. I now know SOoooooooooooooooooooooooooooooo many things can go wrong with your data if DATES are important aspect of your data analysis, and that the date formatting needs to be checked first before proceeding. Thanks

 

[jtakw]

You're welcome, I had suspected that the problem was with your source data, glad you figured it out and got it working.