+ Reply to Thread
Results 1 to 9 of 9

calculate the total time users spend on a spreadsheet per month

  1. #1
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    calculate the total time users spend on a spreadsheet per month

    Hi,

    I have a simple VBS script that puts the username & current time in columns. When the user saves that time is also placed into a column.

    I would like to be able to calculate the amount of time a user has spent on the spreadsheet for the current month & if possible the total time all users have spent on the spreadsheet this months.

    I have uploaded a spreadsheet to explain.

    Thank you in advance.
    Attached Files Attached Files
    Last edited by jpruffle; 08-10-2009 at 04:22 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculate the total time users spend on a spreadsheet per month

    no attachment...

  3. #3
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: calculate the total time users spend on a spreadsheet per month

    Sorry attached now on original

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculate the total time users spend on a spreadsheet per month

    Still not 100% clear what happens in this file, for ex...

    does the file contain transactions for multiple months ?

    do the times cross over months (ie over midnight from one month to another and
    if so what happens ?)

    if using VBA I would use an open event to post today's date (or 1st of current month) into a cell such that you have a static reference available thereby enabling you to avoid the need for the Volatile dependency in subsequent calcs (to ascertain current month)

    so using your sample file... pending answers to above and pending your XL version (assuming pre XL2007)

    Please Login or Register  to view this content.
    Obviously in the above we're using hard wired references, you could create a Dynamic Named Range - see the link in my sig. for more info.

  5. #5
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: calculate the total time users spend on a spreadsheet per month

    Thank you for the reply however the formulas do not appear to be correct.

    This worksheet will update indefinately so when a user opens the file column B has a time stamp DD:MM:YY HH:MM. When they close the file there is another time stampin column C of DD:MM:YY HH:MM. Irrespective of the amount of time they are on it or whether it spans two days column D basically calculates C-B.

    So I am trying to ascertain how much time in total users spent on the spreadsheet for the current month. So the total of all calculations in August. If one of the dates rolls into another month (very unlikely) then I'm not too concerned about the accuracy of that figure.

    Secondly how much time a user has spent on the spreadsheet, so using the calculation above but solely based on the user (in column A).

    I have uploaded a revised copy.

    Thank you in advance
    Attached Files Attached Files

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculate the total time users spend on a spreadsheet per month

    Perhaps format the cells to use a Time Format... say [h]:mm:ss

  7. #7
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: calculate the total time users spend on a spreadsheet per month

    Thank you but for security I need to know the full dates and times...

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculate the total time users spend on a spreadsheet per month

    I have no idea what you're asking for... the formulae provided give exactly your "expected" results so I'm a complete loss I'm afraid.

    I should clarify that my prior post was rhetorical in nature without trying to sound overly sarcastic... time in XL is decimal
    (when less than 24 hours.... 0.5 = 12 hours, when greater than 1 the integer represents days and decimal time remainder ie 1.5 = 1 day 12 hours or 36 hours when formatted to [h]:mm -- given 1 equates to 1 day)

    EDIT: perhaps confusion - the cells to format are those containing the formulae (not the source values themselves) ... ie the results are correct but need to be formatted as time rather than General (so instead of seeing 0.5 you see 12:00)
    Last edited by DonkeyOte; 08-10-2009 at 04:21 PM.

  9. #9
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: calculate the total time users spend on a spreadsheet per month

    I apologise!

    I had no idea about how it was calculated & didn't realise you meant the calculations rather than the date entries.

    Thank you it works great. Sorry for being dumb.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1