#Invalid data type, year function
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
Answers
-
-
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.
-
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
-
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
-
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?
Help Article Resources
Categories
Check out the Formula Handbook template!