Calculating the time in Hours

Excel Help for Calculating The Time In Hours in Automating Data Analysis Tasks

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:   A B C D E F G 1  Agent Name  Activity time Time Description       2  Ros  26/2/2008  3:23:21 PM  Logon       3  Ros  26/2/2008  3:24:14 PM  Ready       4  Ros  26/2/2008  7:05:18 PM  Not Ready       5  Ros  26/2/2008  8:23:23 PM  Ready       6  Ros  26/2/2008  9:31:51 PM  Logoff       7
	Posted by luckyros on 01 Mar 2008

Replies - Displaying 1 to 1 of 1

Order Replies By: Most recent | Chronological | Highest Rated

RE: Calculating Time Productive Rate this: (3/5 from 1 vote) If you sheets are set up exactly this way....I also think you did not enter the correct starting times...i think you meant 2:23 and 2:24 (rather than 3:23 and 3:24) 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 29 Jan 2009

Displaying page 1 of 1