Formula needed - automatically calculate session time totals for each user
 

Hi, new to the forum, hope you peeps can help me!?

I have a CSV file of user data relating to session times on a server.
The columns are as follows: username, uniqueID, loginTime, logoutTime.

What I want to be able to do is calculate for each user:

a) their individual session length of time
b) their total sessions length of time

OK simple enough and I have done this manually thus far by sorting the
data by username and then creating a new column for 'sessionTime' and
another for 'totalSessionTime' with loginTime subtracted from
logoutTime (with the IF to allow for 24Hr time calculations)

I have been able to automate it slightly with

=SUMIF(A1:A50000,"username",G1:G10000)

where "username" represents an actual username that exists.

What I really would like to be able to do is automate the process so
that when the file is updated with the latest data, and therefore
probably new usernames that were not present before, all individual
usernames are calculated automatically without me having to manually
set usernames in each cell formula...

Does this make sense?


--
optimusprym8
------------------------------------------------------------------------
optimusprym8's Profile: http://www.excelforum.com/member.php...o&userid=35665
View this thread: http://www.excelforum.com/showthread...hreadid=554486

Formula needed - automatically calculate session time totals for each user
 

I think this type of thing is best handled with a pivot table. Start off
by opening your CSV. Then click inside the range.

Select Data\Pivot Table
Click next

Drag your user name into the "drop rows here" section
On the pivot table menu, select Pivot Table\formulas\calculated field
Name the field TotalTime
Set formula = endtime -starttime
Click OK


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=554486

Formula needed - automatically calculate session time totals for each user
 

ok thanks will give that a go but what I really want it to do is pull
out the username from the revelant column and do it automatically for
all usernames. Is that possible?


--
optimusprym8
------------------------------------------------------------------------
optimusprym8's Profile: http://www.excelforum.com/member.php...o&userid=35665
View this thread: http://www.excelforum.com/showthread...hreadid=554486