#invalid Datatype Error

Ameya Athalye

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?

Devin Lee

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.

Devin Lee

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

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