Thread: Calculate Total Time

I have to compile a Cisco report monthly and the format is crazy. I looks like this:

12/27/2009 15:59 12/27/2009 16:24 00:24:2b:24:e6:80 Marion_branch 25 min 21 sec 192.168.254.124
12/27/2009 22:08 12/27/2009 22:14 00:23:4d:43:19:1e Marion_branch 5 min 44 sec 10.10.9.196
12/27/2009 22:14 12/27/2009 22:43 00:23:4d:43:19:1e Marion_branch 28 min 48 sec 10.10.9.196


The goal is to calculate the amount of time the wireless access was used on this day without having to add the minutes and seconds manually. Each entry is in a seperate column. A is the connect time, B is the disconnect time, C is the mac address of the laptop, C is the branch name, D is the amount of time the person was connected, and E is the ip address. If there is a way to calcuate the total time per mac address or per branch, it would help so much. Can anyone help me?

Thanks.

Presumably you meant A:F rather than A:E (you duplicated C in your narrative)

Assuming the above to be correct... what is the content/format of E - ie are they numbers or text strings - what does:

=ISNUMBER(E1) return where E1 is (per your example data) "25 min 21 sec"

In essence you should push this data into a Pivot Table - add a header row if you don't have one & give each cell an appropriate value (ie field name).

You can set the PT up such that Mac Address / Branch etc can be Row Fields and you would use Column E (if numeric) as Data Field set to SUM format as Time [hh]:mm:ss

If E is not numeric you could use a calculated field on the PT but it would be simplest to conduct the calc in a new column, ie (assuming header row)

=B2-A2
copied down

and use the new column as DATA Field

If in doubt post a sample file.