My formula is giving me the #INVALID VALUE message and I'm not sure why

Hello! I want to have a column that returns a value of 1 if the date in another column is 6 months or more in the past, and returns 0 if the date is less than 6 months in the past using the following formula:

=IF([Date Accrued]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, DAY(TODAY())), 1, 0)

The formula has resulted in #INVALID VALUE and I can't tell why.

Here is a screenshot of my sheet:

Find more posts tagged with

Formulas

Accepted answers

VBAGuru

@Lindsay Scarey Here is a work around:

=IF([Column3]@row <= DATE(YEAR(TODAY()), IF(MONTH(TODAY()) < 7, MONTH(TODAY()) + 6, MONTH(TODAY()) - 6), DAY(TODAY())), 1, 0)

All comments

VBAGuru

Is your Date Accrued column formatted as Date?

Lindsay Scarey

@VBAGuru yes, it is

VBAGuru

Is Column10 just Text/Number format?

Lindsay Scarey

@VBAGuru yes

VBAGuru

It doesn't seem to like the -6 in the Date formula. Below works for me:

=IF([Column3]@row <= DATE(YEAR(TODAY()), MONTH(TODAY() - 182), DAY(TODAY())), 1, 0)

VBAGuru

I see why now! It doesn't roll the month to 12, with - 6 the month is zero.

VBAGuru

@Lindsay Scarey

Lindsay Scarey

@VBAGuru Aha! Thank you! Do you know the fix for that?

VBAGuru

That was the minus 182

VBAGuru

@Lindsay Scarey Here is a work around:

=IF([Column3]@row <= DATE(YEAR(TODAY()), IF(MONTH(TODAY()) < 7, MONTH(TODAY()) + 6, MONTH(TODAY()) - 6), DAY(TODAY())), 1, 0)

Lindsay Scarey

@VBAGuru Awesome, that worked. Thank you so much!

VBAGuru

@Lindsay Scarey Thinking a bit more, you were going to run into some issues on specific dates. August 29-31 in particular. But also the year would be wrong for the first half of the year. This is what I cam up with:

=IF([Date Accrued]@row <= DATE(IF(MONTH(TODAY()) < 7, YEAR(TODAY()) - 1, YEAR(TODAY())), IF(MONTH(TODAY()) < 7, MONTH(TODAY()) + 6, MONTH(TODAY()) - 6), IF(DAY(TODAY()) > 28, 28, DAY(TODAY()))), 0, 1)

VBAGuru

But also wondering why it needs to be this complex. Could you just use:

=IF([Date Accrued]@row <= Today()-182, 0, 1)

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.