HELP! Column Formula working on some rows - not others

HSabin

Hi!
I'm stumped on this one. I am using a column formula to apply a date 21 days prior to the date in another column. Everything (formatting, etc) is the same on all the rows, but I keep getting "Invalid Value" errors on some rows and in other rows it works great.

Does anyone have any ideas??

Formula: =DATE(YEAR([GT TARGET: ATL BTL C&E Data Deadline]@row), MONTH([GT TARGET: ATL BTL C&E Data Deadline]@row), DAY([GT TARGET: ATL BTL C&E Data Deadline]@row) - 21)

Screenshot:

Mark.poole

@HSabin

The issue here is you are subtracting 21 from the day value. How ever you have this in the DATE function. Creating an issue where if the date you are pulling from is not above 21 it will not return a valid date. Instead move the -21 outside the DATE function.

=DATE(YEAR([GT TARGET: ATL BTL C&E Data Deadline]@row), MONTH([GT TARGET: ATL BTL C&E Data Deadline]@row), DAY([GT TARGET: ATL BTL C&E Data Deadline]@row))-21

MichaelTCA

Hello @HSabin

It is most likely happening because there are not 21 days left in those months.

You can't have any days that are equal to 0 or negative.

Mark.poole

@HSabin

The issue here is you are subtracting 21 from the day value. How ever you have this in the DATE function. Creating an issue where if the date you are pulling from is not above 21 it will not return a valid date. Instead move the -21 outside the DATE function.

=DATE(YEAR([GT TARGET: ATL BTL C&E Data Deadline]@row), MONTH([GT TARGET: ATL BTL C&E Data Deadline]@row), DAY([GT TARGET: ATL BTL C&E Data Deadline]@row))-21

HSabin

Hi @MichaelTCA, I see what you're saying but I thought the formula accounted for month, day and year. Do you have any suggestions on correcting the formula to back out into the previous month?

HSabin

@Mark.poole - thank you! That worked. And just before I saw your reply, I simplified the whole thing by simply typing "=" then clicking on the cell in the column, "-21" and it returned perfectly as well.

THank you!!

Mark.poole

@HSabin

Absolutely. I always try to assume there is a reasoning behind using one function or another. Unless its overly complicated and not working. I am glad you found a solution that works best for you.