Skip to main content
Question

Derive the Year from another field

  • May 21, 2024
  • 0 replies
  • 133 views
ReneeHansen1
Forum|alt.badge.img+6

Hi, 

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

This topic has been closed for replies.

ReneeHansen1
Forum|alt.badge.img+6
  • ReneeHansen1
  • Author
  • Quickbase Alumni
  • May 21, 2024

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])


))))))

MarkShnierYou
Forum|alt.badge.img+22
  • MarkShnierYou
  • Quickbase Alumni
  • May 22, 2024

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.

Mark Shnier. Your Quickbase Coach
Terms & ConditionsAccessibility statement