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

 

 

Example 1

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]

 

 

Example 2

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

 

Top of page

 

Example 3

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

 

 

 

Example 4

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.

 

 

Top of page

 

 

Example 5

Highlight task time that is 10% over budget

CASE WHEN [Hours Worked] > ([Budgeted Hours]*1.1) THEN 'EXAMINE' END

 

 

 

Example 6

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)

 

Top of page

 

 

Example 7

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)   

 

 

Example 8

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)

 

Top of page

 

 

Example 9

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)

 

 

Example 10

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

 

Top of page