#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

Find more posts tagged with

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.