#invalid Datatype Error

Hi all,

I've a formula to calculate estimated hours.

=SUMIFS({Master Summary - Estimated Hours}, {Master Summary Rollup | Live Date}, YEAR(@cell) = [Year Helper]@row, {Master Summary Rollup | Live Date}, MONTH(@cell) = [Month Helper]@row)

The formula uses two references to a Master Summary sheet. Estimated Hours (from Master Summary sheet), Year Helper, Month Helper are all "text/Number" format and Live date (from Master Summary sheet) is a "date" column. I'm getting an invalid datatype error. I tried looping "[Month Helper](@cell)" and "[Year Helper](@row)" in IFERROR, but no luck. This exact formula was working for last two years and have suddenly started giving an error for the past few days.

What do I need to update in my formula?

Tags:

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    I think it's the MONTH(@cell) and YEAR(@cell). Try exchanging it for VALUE(LEFT(@cell,2)) and VALUE(RIGHT(@cell,2)). Might fix it for now but it caps at 100 years which depending on your view point can be an issue.

    =SUMIFS({Master Summary - Estimated Hours}, {Master Summary Rollup | Live Date},VALUE(RIGHT(@cell,2)) = RIGHT([Year Helper]@row,2), {Master Summary Rollup | Live Date}, VALUE(LEFT(@cell,2)) = [Month Helper]@row)
    

    Another option is to add in the Helper columns for the Month and Year on the reference sheet so you can avoid the issue all together.

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    I think it's the MONTH(@cell) and YEAR(@cell). Try exchanging it for VALUE(LEFT(@cell,2)) and VALUE(RIGHT(@cell,2)). Might fix it for now but it caps at 100 years which depending on your view point can be an issue.

    =SUMIFS({Master Summary - Estimated Hours}, {Master Summary Rollup | Live Date},VALUE(RIGHT(@cell,2)) = RIGHT([Year Helper]@row,2), {Master Summary Rollup | Live Date}, VALUE(LEFT(@cell,2)) = [Month Helper]@row)
    

    Another option is to add in the Helper columns for the Month and Year on the reference sheet so you can avoid the issue all together.

  • Ameya Athalye
    Ameya Athalye ✭✭✭✭✭
    edited 01/05/22

    Thanks @Devin Lee, this seems to be working. At least I got a value ( I haven't checked for the correctness yet).


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!