#Invalid data type, year function

Options
JLK
JLK ✭✭
edited 02/01/23 in Formulas and Functions

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

  • JLK
    JLK ✭✭
    edited 02/01/23
    Options
  • DKazatsky
    DKazatsky ✭✭✭
    Options

    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
    JLK ✭✭
    edited 02/01/23
    Options

    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
    DKazatsky ✭✭✭
    Options

    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
    JLK ✭✭
    edited 02/01/23
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!