Derive the Year from another field

Question

Hi,&nbsp;What I want to do:I want the "Year" fields to derive which year it is based on the Business Case Start Date. but I don't want the year to be static.The fields I feel are important:[YEAR 1][YEAR 2][YEAR 3][YEAR 4][YEAR 5][BUSINESS CASE START DATE]&nbsp;- The "YEAR" fields are called just that. They are Formula-Numeric (currency) to let us know how much an employee will cost for that year. The Business case can start at any time during the year, and they go on for 5 years. So really I would need to know the Year of [YEAR 1], and then need to know how I add 1 year for each year after.

reneehansen1 · Answer

This is my formula: - so how could each "year" field be a specific year based on the business case start date (different for each business case)

If(
[Request] = "Add New Resource" and [New Employee Type] = "FTE",
[PRM Level - New FTE Monthly Estimate] * [First Year Duration],

If(
[Request] = "Add New Resource" and [New Employee Type] = "Contractor",
[Contractor Monthly Estimate] * [First Year Duration],

If(
[Request] = "Include Existing Contractor - No Conversion",
[Contractor Monthly Estimate] * [First Year Duration],

If(
[Request] = "Include Existing FTE - Continue or Shift Project",
[FTE PID - Level Estimate - Monthly Estimate] * [First Year Duration],

If(
[Request] = "Add New Contractor + Add New FTE for Conversion",
[Contractor Monthly Estimate] * [First Year Duration],

If(
[Request] = "Include Existing Contractor + Add NEW FTE for Conversion",
[Contractor Monthly Estimate] * [First Year Duration],

If(
[Request] = "New FTE (Related to Conversion)",
[PRM Level - New FTE Monthly Estimate] * [First Year Duration])

))))))

markshnier__you · Answer

I don't really understand your second question as to how it relates to the first question. They seem unrelated. But I will take the liberty of simplifying your current formula because it's written in an Excel type manner which is needlessly complicated with parentheses

IF(
[Request] = "Add New Resource" and [New Employee Type] = "FTE",
[PRM Level - New FTE Monthly Estimate] * [First Year Duration],

[Request] = "Add New Resource" and [New Employee Type] = "Contractor",
[Contractor Monthly Estimate] * [First Year Duration],

[Request] = "Include Existing Contractor - No Conversion",
[Contractor Monthly Estimate] * [First Year Duration],

[Request] = "Include Existing FTE - Continue or Shift Project",
[FTE PID - Level Estimate - Monthly Estimate] * [First Year Duration],

[Request] = "Add New Contractor + Add New FTE for Conversion",
[Contractor Monthly Estimate] * [First Year Duration],

[Request] = "Include Existing Contractor + Add NEW FTE for Conversion",
[Contractor Monthly Estimate] * [First Year Duration],

[Request] = "New FTE (Related to Conversion)",
[PRM Level - New FTE Monthly Estimate] * [First Year Duration])

as for the first question.

The formula for [Year 1] would be

Year([BUSINESS CASE START DATE])

Then the formula for [Year 2] would be [Year 1] + 1.

etc for the rest of Years 3, 4 and 5.

reneehansen1 · Answer

Hi Mark! LOL thank you for deciphering my formula! I will fix it! :)Also - Yes, wow that seems so easy for the Year. I'm sure I'll have follow up questions.&nbsp;&nbsp;

reneehansen1 · Answer

Ok, I do have a follow up question.I also have duration formulas (year 1 duration, year 2 duration, year 3 duration, year 4 duration, year 5 duration). I'm not sure where the formula for the Year would go -&nbsp;Year([BUSINESS CASE START DATE])Then the formula for [Year 2] would be [Year 1] + 1.does it go into the duration formula? Or does it go into the Year $ formula?

markshnier__you · Answer

[Year] will be a formula NUMERIC field type, not duration.Then the formula for [Year 2] would be [Year 1] + 1

Forum Discussion

Derive the Year from another field

6 Replies