| Home > Forum Home > Automating Data Analysis Tasks > Calculating the time in Hours | Share |
Calculating the time in Hours
Excel Help for Calculating The Time In Hours in Automating Data Analysis Tasks
| Forum Topic | Post Reply Login |
Calculating The Time In Hours | Rate this: (3/5 from 1 vote) |
 | Hi, I have agent name is first column(A) from row A2 to A16 same agent name. Activity date in second column(B). The shift starts from 2:30 pm to 9:30 pm. so in the thirrd column(C) from rows C2 to C16 there is time related to that shift. in fourth column(D) rows D2 to D16 the description of the activity done at that time. the activity description can be "Logon" "Ready" "not ready" and "logoff". The shift consist of 7 hours from which half hour will be a break and another 15 mins will be a bio break. so in general an agent must be logged in to the system for atleast 6 hours and 15 minutes in his shift. I am noticing many of them are logged in for less than 6 hours and 15 mins. i want to track this. the "Logoff" and "not ready" has to get deducted from "logon" and "ready". Please help me to solve this problem.Excel Spreadsheet:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Posted by luckyros on | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Replies - Displaying 1 to 1 of 1 | Order Replies By: Most Recent | Chronological | Highest Rated |
| Rate this: (3/5 from 1 vote) Subtract Logon from Logoff in a cell and use custom formatting of [h]:mm:ss This should give you 7:08:30. Your formula will look like: C6-C2 Now that you have the base calculation, we will add to the formula. We want to subtract areas between productive and non-productive and then subtract that from the totail spent. (C6-C2)-((C3-C2)+(C5-C4)) The end result should read as: 5:49:32 |
| Posted by amatwrestler on |
| Displaying page 1 of 1 |
| Find relevant Excel templates and add-ins for Calculating the time in Hours in the Excel Business Solutions Directory |