#Invalid data type, year function

JLK

Hello, I would like to return the year from a MM/DD/YY cell.

Here is the formula I'm using:

I have confirmed that the "event start date no time" is column type date:

But I'm still getting an #Invalid Data Type as the result.

Here is my guess as to why:

The "event Start Date No Time" column is a "helper" column which I used to convert the start date with time column to just a start date, so that I could preserve the time in the previous column. (if I convert that column to date type directly, I loose the time value, which I do need). I want this to remain a column function so that as I add data to this sheet it continues to populate, but I also need for this year formula to pull automatically. Any ideas for how I can get this to work?

Thanks,

Jennie

JLK

DKazatsky

Hi @JLK ,

What is the column type for the Year column? I have replicated using the same formula and am not getting an error. I have the Year column set as Text/Number.

JLK

Yes, the Year column is Text/Number.

I think the issue is that my Date column is already a fx.

I needed a helper row to preserve the time in my original date column, since when you convert to a date data type, you loose the time info.

So, the question is, is there a formula that will allow you to pull the value of the cell rather than the formula that is in that cell?

-Jennie

DKazatsky

I have the same situation with no issue - this is the setup I used.

ColumnA = CreatedDate type

ColumnB = Date type with this column formula: =DATEONLY([ColumnA]@row)

ColumnC = Text/Number type with this column formula: =YEAR([ColumnB]@row)

Hope this helps,

Dave

JLK

Hi Dave,

Thanks for that. It makes sense that that would work but I'm still getting an #INVALID DATA TYPE

First column and second column are both Date type, and third column is text/number. I have no idea why this isn't working.

I was able to get the formula to work by copying and pasting the entire column, but this is not something that is automated, so each time I add data to this sheet, I'll have to manualy copy and past to get this year formula to work which is obviously not idea.

Ideas?