Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
Formula to calculate elapsed time between certain dates and times | Excel Discussion (Misc queries) | |||
How do I set up a formula on a time sheet to calculate time in 1/. | Excel Discussion (Misc queries) | |||
formula to calculate a time sheet | Excel Worksheet Functions | |||
formula to calculate a time sheet | Excel Worksheet Functions |