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

Matt D

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!

Genevieve P.

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.

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

Bassam Khalil

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

Matt D

Hi @Bassam Khalil

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

Bassam Khalil

@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.

Genevieve P.

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

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.

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

@Genevieve P.

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

Genevieve P.

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

@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.

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

Matt D

@Genevieve P.

Yes, here is a screenshot.

Matt D

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.

@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

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.

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