Number of Month Payback ROI

Lila De Vera

Hi everyone,

I have created a form and the pictures below is what is relevant to my question. We have projects with a fiscal year start date and end date, we also want to know the payback date. I did not chose the "date" function on the column as we are only interested in the fiscal year, but maybe there is a work around.

What I am trying to do is automatically calculate the "Investment will be paid back by" column. What I have so far is a calculation that determines how many months and then it is added to the "Fiscal Year Start Date Column" as you can see it produces "6-2020/21", ideally I would like it to show June-2020/21. Something along those lines.

Thanks.

David Joyeuse

Hi @Lila De Vera

To display it as June-2020/21 since it's not a date column, you have two choices:

First is going with a long nested IF formula that I would not recommand.

Second one would be to have an helper sheet that consist of 12 rows with such correspondance:

1 ==> January

2 ==> February

3 ==> March...

...

12 ==> December

Then wrap your ROUNDUP within an INDEX/MATCH to display it:

=INDEX({Month in letter Range}, MATCH(ROUNDUP(([Investment Request]@row / [Return Investment)*12,0), {Month in number Range},0)), + "-" + [Fiscal Year Start Date]@row.

Hope it helped!

David Joyeuse

Hi @Lila De Vera

To display it as June-2020/21 since it's not a date column, you have two choices:

First is going with a long nested IF formula that I would not recommand.

Second one would be to have an helper sheet that consist of 12 rows with such correspondance:

1 ==> January

2 ==> February

3 ==> March...

...

12 ==> December

Then wrap your ROUNDUP within an INDEX/MATCH to display it:

=INDEX({Month in letter Range}, MATCH(ROUNDUP(([Investment Request]@row / [Return Investment)*12,0), {Month in number Range},0)), + "-" + [Fiscal Year Start Date]@row.

Hope it helped!

Lila De Vera

Thanks @David Joyeuse this will work. Ironically bringing me back to the dreaded INDEX/MATCH.

David Joyeuse

@Lila De Vera

You'll notice with experience that both INDEX/MATCH and COLLECT are powerful functions within Smartsheet.

Glad it worked!