#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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!