Turn 1: prompt (sent 2026-11-20T04:46:21.084Z)
In our Workday tenant I need a calculated field on Worker called “Service Band TT-GEM” that we can use in custom reports. It should return one of these text values based on completed years of service up to today: “Under 1 year”, “1 to 3 years”, “3 to 5 years”, “5 years or more”. Use the worker’s Continuous Service Date, and if that is blank use their Hire Date. Exactly 1, 3 or 5 completed years belongs to the higher band (so a worker with exactly 3 completed years is “3 to 5 years”). Please give me what I need to build it.
Turn 1: answer (2026-11-20T04:49:09.186Z, 168.1 s)
To build Service Band TT-GEM as a Text calculated field on Worker, create the component calculated fields in the bottom-up order below using the Create Calculated Field task.
Architecture Overview
Continuous Service Date / Hire Date
└─▶ Effective Service Date (Evaluate Expression - Date)
└─▶ Completed Years of Service (Date Difference - Years vs. Today)
├─▶ Service >= 5 Years (True/False) ──▶ "5 years or more"
├─▶ Service >= 3 Years (True/False) ──▶ "3 to 5 years"
├─▶ Service >= 1 Year (True/False) ──▶ "1 to 3 years"
└─▶ Default ──▶ "Under 1 year"
└─▶ Service Band TT-GEM (Evaluate Expression - Text)Step 1: Create the 4 Text Constants (Business Object: Global)
Create four Text Constant calculated fields (skip any that already exist as reusable constants in your tenant):
| Field Name | Business Object | Function | Text Constant Value |
|---|---|---|---|
Field NameCF TC - Under 1 year - TT-GEM |
Business ObjectGlobal |
FunctionText Constant |
Text Constant ValueUnder 1 year |
Field NameCF TC - 1 to 3 years - TT-GEM |
Business ObjectGlobal |
FunctionText Constant |
Text Constant Value1 to 3 years |
Field NameCF TC - 3 to 5 years - TT-GEM |
Business ObjectGlobal |
FunctionText Constant |
Text Constant Value3 to 5 years |
Field NameCF TC - 5 years or more - TT-GEM |
Business ObjectGlobal |
FunctionText Constant |
Text Constant Value5 years or more |
Step 2: Determine the Effective Service Date (Business Object: Worker)
2a. Check if Continuous Service Date is populated
- Field Name:
CF TF - Continuous Service Date Is Not Blank - TT-GEM - Business Object:
Worker - Function:
True/False Condition - Condition:
- Field:
Continuous Service Date - Operator:
is not empty
- Field:
2b. Return Continuous Service Date, falling back to Hire Date
- Field Name:
CF EE - Effective Service Date - TT-GEM - Business Object:
Worker - Function:
Evaluate Expression - Field Type:
Date - Default Value:
Hire Date(delivered field on Worker) - Condition Grid:
- Condition:
CF TF - Continuous Service Date Is Not Blank - TT-GEM - Return Value if Condition is True:
Continuous Service Date
- Condition:
Step 3: Calculate Completed Years of Service up to Today (Business Object: Worker)
- Field Name:
CF DD - Completed Years of Service - TT-GEM - Business Object:
Worker - Function:
Date Difference - Start Date Field:
CF EE - Effective Service Date - TT-GEM - End Date Field:
Today(Workday-delivered Global field) - Return Value:
Years - Ignore Lower Level Date Components: Unchecked (critical — leaving this unchecked ensures exact anniversary-based completed years rounded down to a whole integer; e.g., 2 years and 364 days returns
2, and exactly 3 years returns3) - Return Zero on Error: Unchecked (recommended so workers missing both dates remain blank rather than returning
0)
Step 4: Create the 3 Threshold Conditions (Business Object: Worker)
Create three True/False Condition fields on Worker, each using Comparison Type: Value specified in this filter:
| Field Name | Field | Operator | Comparison Value |
|---|---|---|---|
Field NameCF TF - Service >= 5 Years - TT-GEM |
FieldCF DD - Completed Years of Service - TT-GEM |
Operatorgreater than or equal to |
Comparison Value5 |
Field NameCF TF - Service >= 3 Years - TT-GEM |
FieldCF DD - Completed Years of Service - TT-GEM |
Operatorgreater than or equal to |
Comparison Value3 |
Field NameCF TF - Service >= 1 Year - TT-GEM |
FieldCF DD - Completed Years of Service - TT-GEM |
Operatorgreater than or equal to |
Comparison Value1 |
Step 5: Create the Final Field — Service Band TT-GEM (Business Object: Worker)
- Field Name:
Service Band TT-GEM - Business Object:
Worker - Function:
Evaluate Expression - Field Type:
Text - Default Value:
CF TC - Under 1 year - TT-GEM - Condition Grid (enter in this exact top-to-bottom order — Workday stops at the first true condition):
| Order | Condition | Return Value if Condition is True |
|---|---|---|
| Order1 | ConditionCF TF - Service >= 5 Years - TT-GEM |
Return Value if Condition is TrueCF TC - 5 years or more - TT-GEM |
| Order2 | ConditionCF TF - Service >= 3 Years - TT-GEM |
Return Value if Condition is TrueCF TC - 3 to 5 years - TT-GEM |
| Order3 | ConditionCF TF - Service >= 1 Year - TT-GEM |
Return Value if Condition is TrueCF TC - 1 to 3 years - TT-GEM |
Verification & Test Cases
Run the Test Calculated Field task on Service Band TT-GEM against workers covering these boundary cases:
| Continuous Service Date | Hire Date | Completed Years vs. Today | Expected Output |
|---|---|---|---|
| Continuous Service Date(blank) | Hire Date364 days ago | Completed Years vs. Today0 |
Expected OutputUnder 1 year |
| Continuous Service DateExactly 1 year ago | Hire Date(any) | Completed Years vs. Today1 |
Expected Output1 to 3 years |
| Continuous Service Date2 years, 364 days ago | Hire Date(any) | Completed Years vs. Today2 |
Expected Output1 to 3 years |
| Continuous Service Date(blank) | Hire DateExactly 3 years ago | Completed Years vs. Today3 |
Expected Output3 to 5 years |
| Continuous Service Date4 years, 364 days ago | Hire Date(any) | Completed Years vs. Today4 |
Expected Output3 to 5 years |
| Continuous Service DateExactly 5 years ago | Hire Date1 year ago (e.g. rehire) | Completed Years vs. Today5 |
Expected Output5 years or more |
Practical Notes & Edge Cases
- Contingent workers / missing dates: If your custom reports include Contingent Workers (where both
Continuous Service DateandHire Datemay be blank), the Default Value above would label them"Under 1 year". To return blank instead:- Create one extra T/F field checking
CF EE - Effective Service Date - TT-GEMis not empty. - Add it as Row 4 returning
CF TC - Under 1 year - TT-GEM. - Set Default Value to a blank/empty Text Constant (or
"N/A").
- Create one extra T/F field checking
- Future-dated hires: For a future start date,
Date Differencereturns0or a negative number, which falls into"Under 1 year". If you want future hires excluded, filter them out in the report or add agreater than or equal to 0check on Row 4. - Terminated workers & historical reports: Using
Todayalways evaluates as of the current run date (even for terminated workers if your report includes them). If you later need point-in-time historical reporting, swapTodayforReport Effective Datein Step 3. - Matrix report sorting tip: Because this returns Text, alphabetical sorting places
"Under 1 year"last. If you mainly use this for grouping in Matrix Reports and want logical tenure sort order without extra sort keys, you can instead build Step 5 usingEvaluate Expression Band(which returns a Single Instance band with inline labels and preserves row definition order—no Text Constants needed). - Security: Users running custom reports with this field need domain view access to both
Continuous Service DateandHire DateonWorker(test with a standard report user, not just an admin).
Turn 2: follow-up (sent 2026-11-20T05:00:15.050Z)
Steps 1 done (four text constants saved). Step 2a says: Field “Continuous Service Date”, Operator “is not empty”. The Operator list on the True/False Condition screen has no “is not empty”. It shows: is blank, is not blank, equal to, not equal to, greater than, greater than or equal to, less than, less than or equal to.
Turn 2: answer (2026-11-20T05:00:29.809Z, 14.8 s)
Use is not blank — in Workday, is blank / is not blank are the operators for Date (and single-value) fields, while is not empty is for multi-instance/list fields.
For Step 2a:
- Field:
Continuous Service Date - Operator:
is not blank(no Comparison Type or Comparison Value needed)
(Same applies if you use the optional missing-date safeguard from the notes later — use is not blank there too.)
For Step 4, greater than or equal to is in the exact list you saw, so you’re all set to continue with Steps 2b through 5. Let me know if any other prompt or option looks different on your tenant.