#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?
Best 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
-
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.
-
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
Categories
Check out the Formula Handbook template!