If Start & Finish date lies in the current year then reflect the value in column "X", row "Y"

Kesaté Tadesse

See diagramed questions in:

RED: IF Start & Finish date lies in the current year then reflect the value in column "X", row "Y".

GREEN: When % complete value turns 100% then reflect the value in column "X", row "Y".

PURPLE: Conversely IF Start & Finish date lies in the next year then reflect the value in column "X", row "Y".

and lastly shown in BROWN: Whats the best formula to show the next year value based on current year i.e it is 2020 now next year is 2021

Eric M Oliveira

Hello,

Welcome to the Smartsheet Community. Happy to help guide you on how to utilize formulas to achieve your desired automation goals. 

All of the below suggested can be achieved utilizing these below functions.

IF: https://help.smartsheet.com/function/if

AND: https://help.smartsheet.com/function/and

YEAR: https://help.smartsheet.com/function/year

TODAY(): https://help.smartsheet.com/function/today

In the formula ="Cash-in | " + YEAR(TODAY()) + 1 you're receiving an undesired result of "Cash-in | 20201" because you're mixing calculative data with text data without distinguishing between the two. Since you're adding text data to the result you'll want to ensure to contain the YEAR information within parenthesis, the desired result would be similar to this:

="Cash-in | " + (YEAR(TODAY()) + 1)

Based on the description provided you will likely want to combine the IF function with the YEAR function, to produce the desired logical calculative results. I've broken them out by color below. 

Purple in your screenshot:

To displays the value of the "Invoice Amount (USD)" column in the cell if the finish date is in the next year you may want to utilize a formula similar to this.

=IF(YEAR(Finish@row)= (YEAR(TODAY()) + 1), [Invoice Amount (USD)]@row)

Red in your screenshot:

To displays the value of the "Invoice Amount (USD)" column in the cell if the finish date and the start date are in the next year you may want to add an AND function to the formula, you could utilize a formula similar to this.

=IF(AND(YEAR(Start@row)= YEAR(TODAY()), YEAR(Finish@row)= YEAR(TODAY())), [Invoice Amount (USD)]@row)

Green in your screenshot

The green desired value is just an extension of the AND function above with the addition of 100%. The formula could be similar to this.

=IF(AND([% Complete]@row=1, YEAR(Start@row)= YEAR(TODAY()), YEAR(Finish@row)= YEAR(TODAY())), [Invoice Amount (USD)]@row)

Have a wonderful day. Thank you for choosing Smartsheet.

Cheers,

Eric

Smartsheet Technical Support

 

Eric M Oliveira

Hello,

Welcome to the Smartsheet Community. Happy to help guide you on how to utilize formulas to achieve your desired automation goals. 

All of the below suggested can be achieved utilizing these below functions.

IF: https://help.smartsheet.com/function/if

AND: https://help.smartsheet.com/function/and

YEAR: https://help.smartsheet.com/function/year

TODAY(): https://help.smartsheet.com/function/today

In the formula ="Cash-in | " + YEAR(TODAY()) + 1 you're receiving an undesired result of "Cash-in | 20201" because you're mixing calculative data with text data without distinguishing between the two. Since you're adding text data to the result you'll want to ensure to contain the YEAR information within parenthesis, the desired result would be similar to this:

="Cash-in | " + (YEAR(TODAY()) + 1)

Based on the description provided you will likely want to combine the IF function with the YEAR function, to produce the desired logical calculative results. I've broken them out by color below. 

Purple in your screenshot:

To displays the value of the "Invoice Amount (USD)" column in the cell if the finish date is in the next year you may want to utilize a formula similar to this.

=IF(YEAR(Finish@row)= (YEAR(TODAY()) + 1), [Invoice Amount (USD)]@row)

Red in your screenshot:

To displays the value of the "Invoice Amount (USD)" column in the cell if the finish date and the start date are in the next year you may want to add an AND function to the formula, you could utilize a formula similar to this.

=IF(AND(YEAR(Start@row)= YEAR(TODAY()), YEAR(Finish@row)= YEAR(TODAY())), [Invoice Amount (USD)]@row)

Green in your screenshot

The green desired value is just an extension of the AND function above with the addition of 100%. The formula could be similar to this.

=IF(AND([% Complete]@row=1, YEAR(Start@row)= YEAR(TODAY()), YEAR(Finish@row)= YEAR(TODAY())), [Invoice Amount (USD)]@row)

Have a wonderful day. Thank you for choosing Smartsheet.

Cheers,

Eric

Smartsheet Technical Support

 

Kesaté Tadesse

Dear Eric,

Thank you for this. I works well.

Similar to the "Projected Revenue | This Year (USD)" column, I wanted to further break the projections quarterly.

i.e: If the finish date is the current year and the month of finish date is between MONTH 1, 2 & 3 (Jan, Feb & Mar)......then reflect "Invoice Amount (USD) @ row.