#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?

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

Devin Lee

=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).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.