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 team, combine the two into one column.
NOTE: Data Type must be set to 'Text'
[Employee First and Last Name]+' - '+[Team Name]
Show percentage completed (Actual Hours Worked/Budgeted Hours)
NOTE: Data Type must be set to 'Number'
* 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.
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'
Sort 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 - 2015'
(CASE WHEN DATEPART(QUARTER,[Time Entry Date]) = 1 and YEAR([Time Entry Date]) = 2015 THEN [Actual Company Cost] ELSE 0 END)
Calculated column #2 labeled 'Q2 - 2015'
(CASE WHEN DATEPART(QUARTER,[Time Entry Date]) = 2 and YEAR([Time Entry Date]) = 2015 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 [Overtime Charge Applied] = 'Yes' then 'Jan - OT' else 'Jan - Reg' end) when Month([Time Entry Date]) = 2 then (case when [Overtime Charge Applied] = 'Yes' then 'Feb - OT' else 'Feb - Reg' end) when Month([Time Entry Date]) = 3 then (case when [Overtime Charge Applied] = 'Yes' then 'Mar - OT' else 'Mar - Reg' end) when Month([Time Entry Date]) = 4 then (case when [Overtime Charge Applied] = 'Yes' then 'Apr - OT' else 'Apr - Reg' end) when Month([Time Entry Date]) = 5 then (case when [Overtime Charge Applied] = 'Yes' then 'May - OT' else 'May - Reg' end) when Month([Time Entry Date]) = 6 then (case when [Overtime Charge Applied] = 'Yes' then 'June - OT' else 'June - Reg' end) when Month([Time Entry Date]) = 7 then (case when [Overtime Charge Applied] = 'Yes' then 'Jul- OT' else 'Jul - Reg' end) when Month([Time Entry Date]) = 8 then (case when [Overtime Charge Applied] = 'Yes' then 'Aug - OT' else 'Aug - Reg' end) when Month([Time Entry Date]) = 9 then (case when [Overtime Charge Applied] = 'Yes' then 'Sep - OT' else 'Sep - Reg' end) when Month([Time Entry Date]) = 10 then (case when [Overtime Charge Applied] = 'Yes' then 'Oct - OT' else 'Oct - Reg' end) when Month([Time Entry Date]) = 11 then (case when [Overtime Charge Applied] = 'Yes' then 'Nov - OT' else 'Nov - Reg' end) when Month([Time Entry Date]) = 12 then (case when [Overtime Charge 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
