Practical Scenarios for Common Functions
This document provides real-world condition examples and use cases for each commonly
used function.
1. Mathematical Function Scenarios
ABS(): Scenario: Calculate the absolute variance between actual and target sales.
Example: ABS([Actual Sales] - [Target Sales])
ROUND(): Scenario: Round employee performance scores to nearest whole number.
Example: ROUND([Performance_Score], 0)
POWER(): Scenario: Calculate compound growth in a financial model.
Example: POWER((1 + [GrowthRate]), [Years])
LOG10(): Scenario: Normalize large numerical data for analytics.
Example: LOG10([Revenue])
DOUBLE(): Scenario: Convert text-form numbers to numeric format for calculations.
Example: DOUBLE('45.67')
2. String Function Scenarios
CONCAT(): Scenario: Combine first name, middle name, and last name to form full name.
Example: CONCAT([FirstName], ' ', [LastName])
LEFT(): Scenario: Extract first 3 characters of employee code.
Example: LEFT([EmpCode], 3)
RIGHT(): Scenario: Extract last 4 digits of employee ID.
Example: RIGHT([EmpID], 4)
SUBSTRING(): Scenario: Extract department code from string like 'Dept-IT-001'.
Example: SUBSTRING([DeptString], 6, 2)
UPPERCASE(): Scenario: Standardize names in uppercase format.
Example: UPPERCASE([City])
LOWERCASE(): Scenario: Convert all email addresses to lowercase.
Example: LOWERCASE([Email])
REPLACE(): Scenario: Replace '-' with '/' in date strings.
Example: REPLACE([DateString], '-', '/')
LENGTH(): Scenario: Validate phone numbers that must have 10 digits.
Example: IF(LENGTH([Phone])=10,'Valid','Invalid')
LIKE(): Scenario: Filter employee emails that contain '@[Link]'.
Example: LIKE([Email], '%@[Link]%')
LOCATE(): Scenario: Find where '@' appears in email.
Example: LOCATE('@', [Email])
3. Logical and Conditional Scenarios
IF(): Scenario: Determine if an employee qualifies for a bonus.
Example: IF([PerformanceRating]>=4, 'Bonus Eligible', 'Not Eligible')
ISNULL(): Scenario: Replace blank department names with 'Unassigned'.
Example: IF(ISNULL([Department]), 'Unassigned', [Department])
NOT(): Scenario: Exclude inactive employees.
Example: IF(NOT([IsActive]), 'Inactive', 'Active')
4. Date and Time Function Scenarios
DAY(): Scenario: Extract the joining day from hire date.
Example: DAY([HireDate])
MONTH(): Scenario: Identify employees hired in December.
Example: IF(MONTH([HireDate])=12,'Yes','No')
YEAR(): Scenario: Display the hire year for tenure tracking.
Example: YEAR([HireDate])
WEEK(): Scenario: Determine which week a new hire joined.
Example: WEEK([HireDate])
QUARTER(): Scenario: Identify recruitment quarter.
Example: QUARTER([HireDate])
DAYS_BETWEEN(): Scenario: Calculate employee tenure in days.
Example: DAYS_BETWEEN([HireDate], CURRENTDATE())
ADDDAYTODATE(): Scenario: Compute project review date 15 days after start.
Example: ADDDAYTODATE([StartDate], 15)
ADDMONTHTODATE(): Scenario: Calculate next appraisal date (12 months later).
Example: ADDMONTHTODATE([HireDate], 12)
UTCTOLOCAL(): Scenario: Convert login time from UTC to IST.
Example: UTCTOLOCAL([LoginTime], 'IST')
5. Conversion and Utility Scenarios
TOTEXT(): Scenario: Convert numerical employee IDs to text for concatenation.
Example: TOTEXT([EmpID])
TOINTEGER(): Scenario: Convert text salary field into numeric for calculations.
Example: TOINTEGER([SalaryText])
TONUMBER(): Scenario: Parse stored numeric text values for analysis.
Example: TONUMBER([ScoreText])