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

Lindsay Scarey

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:

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)

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)