HELP! Column Formula working on some rows - not others

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:

Tags:

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/11/24 Answer ✓

    @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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    edited 06/11/24

    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
    Mark.poole ✭✭✭✭✭✭
    edited 06/11/24 Answer ✓

    @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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • HSabin
    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
    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
    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!