Smartsheet cannot determine >= and <= between dates when column property is not Date

Options

I have columns Start Month and Finish Month, formatted as 1-2021, 2-2021, etc. I am trying to automatically populate an equally divided cost across my month columns based on the duration between the Start Month and Finish Month. I have a helper row at the top of my sheet under each month column to reference in my formula, since Smartsheet does not allow you to reference the name of a column. My formula follows:

=IF(OR([Jan-2020]$1 >= $[Start (Month)]@row, [Jan-2020]$1 <= $[Finish (Month)]@row), $[PO Cost]@row / $[Duration (Months)]@row)

The problem is Smartsheet does not seem to answer to my ranges because the Start Month, Finish Month, and helper row are not formatted as Date. For example, Smartsheet cannot determine that 1-2020 is not greater than 10-2020 when I drag the formula across the row. I can format my Start/Finish Month columns as Date, but I cannot do the same to my helper row; therefore, I still have a disconnect in my formula. Any feedback is greatly appreciated! Thanks!


Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Matt D

    You are correct, the formula would need to be able to recognize the numbers in the cell as dates in order to identify if they're greater-than or less-than other dates.

    I would recommend turning your two Start and Finish columns into date type of columns. Then we can use the DATE function in your formula to convert numbers into dates.

    Ex:

    =IF(OR(DATE(2020, 1, 1) >= $[Start (Month)]@row, DATE(2020, 1, 31) <= $[Finish (Month)]@row), $[PO Cost]@row / $[Duration (Months)]@row)


    The example above would need you to update the formula for each column, so February would be as follows:

    =IF(OR(DATE(2020, 2, 1) >= $[Start (Month)]@row, DATE(2020, 2, 28) <= $[Finish (Month)]@row), $[PO Cost]@row / $[Duration (Months)]@row)


    I will note that because you have an OR in here, if the Start Date is in the future (compared to that month) and the End Date is ALSO in the future, one of your criteria will be correct and the formula will give you a calculation. The same thing will happen if both dates are in the past.

    I would actually write this statement backwards, identifying when to be blank, and only returning the Cost/Duration if the statement is false, like so:

    Suggested Formula:

    =IF(OR(AND(DATE(2020, 1, 1) > $[Start (Month)]@row, DATE(2020, 1, 1) > $[Finish (Month)]@row), AND(DATE(2020, 1, 31) < $[Start (Month)]@row, DATE(2020, 1, 31) < $[Finish (Month)]@row)), "", $[PO Cost]@row / $[Duration (Months)]@row)


    This says, If BOTH the Start and Finish dates in this row are both before Jan 1st, 2020, then be blank. OR if BOTH the Start and Finish dates are later than Jan 31st, 2020, also be blank. Otherwise, return the Cost over Duration.

    Does this make sense?

    There would be a way to reference the top row instead, but it would require getting a bit more complex, grabbing the LEFT and RIGHT values from that cell and then comparing it using MONTH and YEAR functions. Let me know if you'd prefer this and I can get some examples of this version as well.

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Matt D

    I would actually use the MONTH function based off of your Start and End dates instead of looking at the Days. You could have a task that's only 3 days but it starts in one month and ends in the other.

    This is what I would write:

    =IF(YEAR([Start (Month)]@row) = YEAR([Finish (Month)]@row), MONTH([Finish (Month)]@row) - MONTH([Start (Month)]@row) + 1, IF(YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row) = 1, (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row), (YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row)) * 12 - 12 + (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row)))


    It looks complicated but let me break it down.


    First Statement:

    =IF(YEAR([Start (Month)]@row) = YEAR([Finish (Month)]@row), MONTH([Finish (Month)]@row) - MONTH([Start (Month)]@row) + 1,


    This says, if the Year is the same in the Start and End dates, then minus the Finish Month from the Start Month and add 1.


    Second Statement:

    IF(YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row) = 1, (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row),


    Otherwise, if the Finish Date's Year is the next year (so, a difference of 1), then find how many Months are left to go in this year from the Start Date and then find how many months into the next year the Finish date is and add these together.


    Final Statement:

    (YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row)) * 12 - 12 + (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row)))


    Otherwise (if the Year is greater than just 1 year different), find how many years different these tasks are and multiply that by 12 to find the Months. Remove one year's months ( - 12) because we need to calculate the specific months as we did before... taking how many are left to go from the Start of the task and adding it to how far along the Finish month is.


    Does this make sense?

    Cheers,

    Genevieve

«1

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Matt D

    Hope you are fine, your problem as you said because of date formatting because you are comparing Text with Date.

    you can solve this problem in different approach if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Matt D
    Matt D ✭✭
    Options

    Hi @Bassam Khalil

    I do not want to share my sheet. What is the different approach?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Matt D

    Am not saying share your sheet, i said share a copy of your sheet containing sample data to help me to create the formula for you using your criteria and your column names so when you copy it it to your original sheet it will work.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Matt D

    You are correct, the formula would need to be able to recognize the numbers in the cell as dates in order to identify if they're greater-than or less-than other dates.

    I would recommend turning your two Start and Finish columns into date type of columns. Then we can use the DATE function in your formula to convert numbers into dates.

    Ex:

    =IF(OR(DATE(2020, 1, 1) >= $[Start (Month)]@row, DATE(2020, 1, 31) <= $[Finish (Month)]@row), $[PO Cost]@row / $[Duration (Months)]@row)


    The example above would need you to update the formula for each column, so February would be as follows:

    =IF(OR(DATE(2020, 2, 1) >= $[Start (Month)]@row, DATE(2020, 2, 28) <= $[Finish (Month)]@row), $[PO Cost]@row / $[Duration (Months)]@row)


    I will note that because you have an OR in here, if the Start Date is in the future (compared to that month) and the End Date is ALSO in the future, one of your criteria will be correct and the formula will give you a calculation. The same thing will happen if both dates are in the past.

    I would actually write this statement backwards, identifying when to be blank, and only returning the Cost/Duration if the statement is false, like so:

    Suggested Formula:

    =IF(OR(AND(DATE(2020, 1, 1) > $[Start (Month)]@row, DATE(2020, 1, 1) > $[Finish (Month)]@row), AND(DATE(2020, 1, 31) < $[Start (Month)]@row, DATE(2020, 1, 31) < $[Finish (Month)]@row)), "", $[PO Cost]@row / $[Duration (Months)]@row)


    This says, If BOTH the Start and Finish dates in this row are both before Jan 1st, 2020, then be blank. OR if BOTH the Start and Finish dates are later than Jan 31st, 2020, also be blank. Otherwise, return the Cost over Duration.

    Does this make sense?

    There would be a way to reference the top row instead, but it would require getting a bit more complex, grabbing the LEFT and RIGHT values from that cell and then comparing it using MONTH and YEAR functions. Let me know if you'd prefer this and I can get some examples of this version as well.

    Cheers,

    Genevieve

  • Matt D
    Matt D ✭✭
    Options

    Hi @Genevieve P.

    Thank you so much for the insightful response! I think this will work, however I am confused as to what exactly in my sheet the DATE(yyyy,mm,dd) function is referencing in the formula? Also, I changed my original OR formula to AND, as it seems to work better this way. Any clarification is appreciated (:

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Matt D

    The DATE function isn't referencing anything in your sheet, it would need to be defined by you per-column. It's like writing "January" right into the formula itself, does that make sense?

    DATE(2020, 1, 1) = January 1st, 2020


    So you'd need to customize this once per-column.

  • Matt D
    Matt D ✭✭
    Options

    @Genevieve P.

    Yes, this makes sense. I tried using the formula you suggested and it is returning INVALID OPERATION. Any suggestions?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Matt D

    You'll get INVALID OPERATION if the Start (Month)] and [Finish (Month)] columns aren't set to a Date Type of column, or if the values in those cells aren't dates (ex. 2 - 2020 instead of a date selected). Could that be the issue?

  • Matt D
    Matt D ✭✭
    Options

    @Genevieve P.

    I set the Start and Finish columns to Date Type, so I am not sure where the error is? The formula seems correct to my eye but I could be missing something.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    @Matt D Would you be able to provide a screen capture, with the formula open? Similar to your first image above.

  • Matt D
    Matt D ✭✭
    Options

    @Genevieve P.

    Yes, here is a screenshot.


  • Matt D
    Matt D ✭✭
    Options

    Hi @Genevieve P.

    If this formula does not work, would you be able to provide me with an option for referencing the top row instead?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    @Matt D

    The formula is structured correctly and picking up the correct cells. I'd like to identify what Operation it's seeing as Invalid. Can you try these individual elements to see which one gives you an error?

    =IF(DATE(2020, 1, 1) > $[Start (Month)]@row, "Starts Before Jan", "Starts After Jan")


    =IF(DATE(2020, 1, 1) > $[Finish (Month)]@row, "Finishes Before Jan", "Finishes After Jan")


    = $[PO Cost]@row / $[Duration (Months)]@row


    Then if the two formulas referencing your Start and Finish columns return an error, try this:

    =IF(ISDATE($[Start (Month)]@row), "Recognized as Date", "Text Value")

    =IF(ISDATE($[Finish (Month)]@row), "Recognized as Date", "Text Value")


    Let me know the results of your test!

  • Matt D
    Matt D ✭✭
    Options

    Hi @Genevieve P.

    I cannot thank you enough for your help! I found an error in my source sheet from which my Start/Finish columns were linked. Now that the error is corrected, your formula works wonderfully!

    Follow up question for you:

    When calculating my Duration (Months), the number returned is not exactly what I need to divide the PO Cost by. For example, if Start is 6/15 and Finish is 7/15, my Duration (Months) formula returns 1. However, I need it to return 2 because the cost is divided between the two months in which the work occurs.

    Duration (Months) formula: =ROUND(Duration@row / 30) + 1

    (Duration@row is the duration (days) from my Start/Finish columns)

    Similarly, if Start is 6/1 and Finish is 6/15, Duration (Months) returns 0, but I need 1. I essentially need to be able to divide the PO Cost by the number of months columns it ranges in my sheet.

    Again, you have been a tremendous help. Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Matt D

    I would actually use the MONTH function based off of your Start and End dates instead of looking at the Days. You could have a task that's only 3 days but it starts in one month and ends in the other.

    This is what I would write:

    =IF(YEAR([Start (Month)]@row) = YEAR([Finish (Month)]@row), MONTH([Finish (Month)]@row) - MONTH([Start (Month)]@row) + 1, IF(YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row) = 1, (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row), (YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row)) * 12 - 12 + (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row)))


    It looks complicated but let me break it down.


    First Statement:

    =IF(YEAR([Start (Month)]@row) = YEAR([Finish (Month)]@row), MONTH([Finish (Month)]@row) - MONTH([Start (Month)]@row) + 1,


    This says, if the Year is the same in the Start and End dates, then minus the Finish Month from the Start Month and add 1.


    Second Statement:

    IF(YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row) = 1, (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row),


    Otherwise, if the Finish Date's Year is the next year (so, a difference of 1), then find how many Months are left to go in this year from the Start Date and then find how many months into the next year the Finish date is and add these together.


    Final Statement:

    (YEAR([Finish (Month)]@row) - YEAR([Start (Month)]@row)) * 12 - 12 + (13 - MONTH([Start (Month)]@row)) + MONTH([Finish (Month)]@row)))


    Otherwise (if the Year is greater than just 1 year different), find how many years different these tasks are and multiply that by 12 to find the Months. Remove one year's months ( - 12) because we need to calculate the specific months as we did before... taking how many are left to go from the Start of the task and adding it to how far along the Finish month is.


    Does this make sense?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!