Return blank when grabbing the year from the date in a different cell

**Hedy** · 05-08-2018, 12:22 PM

Cells in column B have dates in them such as 1/1/2017, etc

I am bringing over the year only from this date into column A
I want to ignore any blanks that there may be in column B (the column with the full date)
Right now my formula is simple: =YEAR(B2)

How/what do I add to this formula to ignore the blanks in column B?

Thank you

**Richard Buttrey** · 05-08-2018, 12:27 PM

Hi,

Presumably...

Formula:

Please Login or Register  to view this content.

**Hedy** · 05-08-2018, 01:02 PM

Hello,
I thought that would work too, but it doesn't. I checked my formatting and that isn't the issue. I am still seeing #VALUE! for the cells that are blank.

**Sam Capricci** · 05-08-2018, 02:06 PM

is it possible the item in the "blank" cell isn't really blank? For example could there be a space in the cell? Because if I hit the space bar and then point that formula at it I'll get a value error.

**Richard Buttrey** · 05-08-2018, 02:08 PM

Originally Posted by Hedy

Hello,
I thought that would work too, but it doesn't. I checked my formatting and that isn't the issue. I am still seeing #VALUE! for the cells that are blank.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

**Hedy** · 05-08-2018, 02:22 PM

As suggested by one of the responses, the user entered N/A into the field, and that is why I am getting the #VALUE! result. The formula is perfect - thank you.

That said, how/where would I add to this formula to leave the host cell blank if N/A is in B2?

Original formula from Richard:

=IF(B2="","",YEAR(B2))

**Sam Capricci** · 05-08-2018, 02:26 PM

if N/A is not the result of an error but hard coded then this modification to Richard's formula would work...
=IF(OR(B2="",B2="N/A"),"",YEAR(B2))
if it is the result of an error then...
=IFERROR(IF(B2="","",YEAR(B2)),"")

**Hedy** · 05-08-2018, 03:17 PM

Thank you both for your great help!

**Sam Capricci** · 05-08-2018, 03:50 PM

You're welcome AND thank you for the rep!

Return blank when grabbing the year from the date in a different cell

LinkBack

Thread Tools

Rate This Thread

Display

Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Re: Return blank when grabbing the year from the date in a different cell

Thread Information

Users Browsing this Thread

Similar Threads

return value if date is between two other dates, depending on the year of another cell

[SOLVED] return value if date is between two other dates, depending on the year of another cell

If year of a date matches the year of any date in a range of dates, return true

Return True if date in cell is between a month and year in drop down comboboxes

[SOLVED] Date formula to return blank if cell is blank

Display 'Date' cell as blank instead of default year 1900

Formula to return year of date in a cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions