Knowledgebase
Region IX Education Cooperative > Region 9 Support > Knowledgebase

Time Tag Pivot Tables - Windows PC Office 2013 Instructions

Solution

Windows Computers - Excel 2010

Reports to Calculate Employee’s Days Worked

  • In Time Tag go to reports and select the users you want to access data on.

    • type their names and select all you want

      • choose the date range you are interested in

        • ie:  all time • or this month your choice

      • Select details

        • choose Show entries per “user” grouped by “day”

        • export to csv the results

  • Drop on excel or open excel and import data and choose the csv file you just exported

    • select the data you are working with:  A1 to bottom right of data

      • click once on A1 then scroll to bottom right of data and “shift click”

    • choose pivot table (Insert > Pivot Table > OK) and make the following selections.

      • duration minutes - “drag” - put in summation / values box (bottom right)

      • date - put in Row labels

      • user - put in Report Filter

      • category - put in Report Filter

        • *** important - Leave Column Labels Blank



    • To calculate ½ days and full days based on 3.75 hr ½ work day & 7.5 hr full work day

      • Enter the following formula in the cell next to the first value in the “Sum of Duration (Minutes) Column.  In this example, cell C5. B5 referenced in the formula is the first cell with time worked in it.

      • =IF(B5>=450, 1, IF(B5>=225, 0.5, 0))

        • Now, click C5 once, and drag the bottom right corner down to copy this formula to all of the cells next to those with a value in column B.

Optional:  

Conditional Formatting to make data “jump”

    • To highlight the entries below 450 minutes

      • Go to Home > Styles > Conditional Formatting > New Rule

        • Format all cells based on their values

        • 2-Color Scale

        • Minimum > Type: Number > Value: 449.99

        • Maximum > Type: Number > Value 450.00



 

  • Format the spreadsheet in any format you want

    • add any data you want to type beside or below

 

  • there you go



 
Was this article helpful? yes / no
Related articles Time Tag Pivot Tables - Mac Instructions
Article details
Article ID: 4
Category: Knowledgebase
Date added: 2016-04-11 16:22:51
Views: 137
Rating (Votes): Article not rated yet (0)

 
« Go back

 
Powered by Help Desk Software HESK, brought to you by SysAid