Calculated Column examples
The following are examples of Calculated Columns using combinations of the user interface options along with string, numeric and date functions.
Example 1: Combine 2 text strings together
Example 2: Show percentage completed (Actual Hours Worked/Budgeted Hours)
Example 3: Display time by month
Example 4: Display costs by quarter
Example 5: Highlight task time that is 10% over budget
Example 6: Display the month a time entry is entered and whether it is Overtime or not
Example 7: Show an employee's last name and first 2 letters of first name
Example 8: Show the Week day name for each time entry
Example 9: Convert time entry hours from decimal to hh:mm
Example 10: Only show hours for projects whose name include 'supp'
Description of Available columns
Advanced Calculated Column functions
Combine 2 text strings together
Instead of having a column showing the employee name and another column showing the employee's group name, combine the two into one column.
NOTE: Data Type must be set to 'Text'
[Employee First and Last Name]+' - '+[Employee Group Name]
Show percentage completed (Actual Hours Worked/Budgeted Hours)
* Note that this example has the potential of returning a divide by zero result and as such has a CASE statement added to bypass any such event.
NOTE: Data Type must be set to 'Number'
CASE [Budgeted Hours] WHEN 0 THEN 0 ELSE [Hours Worked]/[Budgeted Hours]*100 END
Time report broken up by Month - * Requires 1 calculated column for each month.
Calculated column #1 labeled Jan - (CASE WHEN MONTH([Time Entry Date]) = 1 THEN [Hours Worked] ELSE 0 END)
Calculated column #2 labeled Feb - (CASE WHEN MONTH([Time Entry Date]) = 2 THEN [Hours Worked] ELSE 0 END)
Calculated column #3 labeled Mar - (CASE WHEN MONTH([Time Entry Date]) = 3 THEN [Hours Worked] ELSE 0 END)
etc.
Notes:
-
For projects that span a calendar year, you can add an additional calculated field to distribute the time by year and month.
-
Data Type must be set to 'Text'
-
Group by Year and then Project
Year([Time Entry Date])
Display Costs by Quarter
* Requires 1 calculated column for each quarter.
* In Dovico, Quarter 1 = January to March, Quarter 2 = April to June, etc.
Calculated column #1 labeled 'Q1 - 2016'
(CASE WHEN DATEPART(QUARTER,[Time Entry Date]) = 1 and YEAR([Time Entry Date]) = 2017 THEN [Actual Company Cost] ELSE 0 END)
Calculated column #2 labeled 'Q2 - 2016'
(CASE WHEN DATEPART(QUARTER,[Time Entry Date]) = 2 and YEAR([Time Entry Date]) = 2017 THEN [Actual Company Cost] ELSE 0 END)
etc.
Highlight task time that is 10% over budget
CASE WHEN [Hours Worked] > ([Budgeted Hours]*1.1) THEN 'EXAMINE' END
Display the month a time entry is entered and whether it is Overtime or not
NOTE: Data Type must be set to 'Text'
(case when Month([Time Entry Date]) = 1 then (case when [Billing Overtime Applied] = 'Yes' then 'Jan - OT' else 'Jan - Reg' end) when Month([Time Entry Date]) = 2 then (case when [Billing Overtime Applied] = 'Yes' then 'Feb - OT' else 'Feb - Reg' end) when Month([Time Entry Date]) = 3 then (case when [Billing Overtime Applied] = 'Yes' then 'Mar - OT' else 'Mar - Reg' end) when Month([Time Entry Date]) = 4 then (case when [Billing Overtime Applied] = 'Yes' then 'Apr - OT' else 'Apr - Reg' end) when Month([Time Entry Date]) = 5 then (case when [Billing Overtime Applied] = 'Yes' then 'May - OT' else 'May - Reg' end) when Month([Time Entry Date]) = 6 then (case when [Billing Overtime Applied] = 'Yes' then 'June - OT' else 'June - Reg' end) when Month([Time Entry Date]) = 7 then (case when [Billing Overtime Applied] = 'Yes' then 'Jul- OT' else 'Jul - Reg' end) when Month([Time Entry Date]) = 8 then (case when [Billing Overtime Applied] = 'Yes' then 'Aug - OT' else 'Aug - Reg' end) when Month([Time Entry Date]) = 9 then (case when [Billing Overtime Applied] = 'Yes' then 'Sep - OT' else 'Sep - Reg' end) when Month([Time Entry Date]) = 10 then (case when [Billing Overtime Applied] = 'Yes' then 'Oct - OT' else 'Oct - Reg' end) when Month([Time Entry Date]) = 11 then (case when [Billing Overtime Applied] = 'Yes' then 'Nov - OT' else 'Nov - Reg' end) when Month([Time Entry Date]) = 12 then (case when [Billing Overtime Applied] = 'Yes' then 'Dec - OT' else 'Dec - Reg' end) else '' end)
Show an employee's last name and first 2 letters of first name
NOTE: Data Type must be set to 'Text'
[Employee Last Name]+', '+SUBSTRING([Employee First Name],1,2)
Show the Week day name for each time entry
NOTE: Data Type must be set to 'Text'
CASE WHEN Datepart(WEEKDAY,[Time Entry Date]) = 1 then 'Sunday' when Datepart(WEEKDAY,[Time Entry Date]) = 2 then 'Monday' when Datepart(WEEKDAY,[Time Entry Date]) = 3 then 'Tuesday' when Datepart(WEEKDAY,[Time Entry Date]) = 4 then 'Wednesday' when Datepart(WEEKDAY,[Time Entry Date]) = 5 then 'Thursday' when Datepart(WEEKDAY,[Time Entry Date]) = 6 then 'Friday' when Datepart(WEEKDAY,[Time Entry Date]) = 7 then 'Saturday' else '' end)
Convert time entry hours from decimal to hh:mm (Example display 1:30 instead of 1.50
NOTES:
-
Data Type must be set to 'Text'
-
The column data cannot be rolled up to the employee level, task level, etc.
-
The screen shot below displays both the 'decimal hours' column and the 'hh:mm' column
CAST(FLOOR(CAST([Hours Worked] as decimal(10,2))) as varchar(2)) + ':' + RIGHT(Cast(CAST(([Hours Worked]-FLOOR(CAST([Hours Worked] as decimal(10,2)))) * 0.6 as DECIMAL(10,2)) as varchar(10)),2)
Only show hours for projects whose name include 'Supp'
NOTES:
-
Data Type must be set to 'Number'
Case when [Project Name] like '%supp%' then [Hours Worked] ELSE 0 END
