Number of Month Payback ROI

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.

Find more posts tagged with

Accepted answers

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!

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.