Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Tracking work hours

    Got some really good help on desk top shortcuts. Now I have another query.
    What I want to do is track hours over any period i.e. the last 24 hours or the last 168 hours (a week).
    I might work from 8:00 am to 11:00 am then 3:00 pm to 6:00 pm. I subtract the start from the finish time then sum the hours. This part is easy. What I now want to do is count the number of hours worked in the last 24 hours or the last week and be able to flag anything that exceeds a certain number of hours.
    Im thinking something like
    Now() countif
    But not sure how to put it together.
    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a sample file with how your data is set up? The solution will be different if dates are stored separately from the times and how the entire dataset is setup...

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    ozcee (2011-07-04)

  4. #3
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Day start time finish time Time start time finish time Time start time finish time Time start time finish time Time start time finish time Time total time
    Monday 7:00 11:00 4:00 12:00 16:00 4:00 17:00 20:00 3:00 11:00
    Tuesday 7:00 10:00 3:00 10:30 16:00 5:30 18:00 22:00 4:00 12:30
    Wednesday 7:00 10:30 3:30 11:30 17:00 5:30 19:00 23:00 4:00 13:00
    Thursday 8:00 10:00 2:00 12:30 16:00 3:30 20:00 0:00 4:00 1:00 3:00 2:00 2:30 5:00 2:30 14:00
    Friday 9:00 12:30 3:30 13:30 19:00 5:30 21:00 1:00 4:00 13:00
    Saturday 10:00 13:30 3:30 14:30 20:00 5:30 22:00 2:00 4:00 13:00
    Sunday 11:00 14:30 3:30 15:30 21:00 5:30 23:00 3:00 4:00 13:00

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a sample workbook? [Go Advanced - Manage Attachments - Browse - upload]

    How are you distinguishing weeks? Some days in your list seem to have more values than others?

    what daily max and weekly max hours do you want to "flag"?
    Steve
    Last edited by sdckapr; 2011-07-02 at 06:29.

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    ozcee (2011-07-04)

  7. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is something like this what you are after? The values in yellow are what you fill in (I used your example data). The cells in grey are calculations. I set the max day at 13 hrs and the max week at 80 and used conditional formatting to mark the cells red when the daily and weekly values are over this. Change the values as desired.

    Steve
    Attached Files Attached Files

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    ozcee (2011-07-04)

  9. #6
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Steve
    Thanks very much. This worked a treat. Sorry I took so long to answer but I had to work (oh-hum). I now have my spreadsheet doing just about all I need
    Thanks Cliff

  10. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    New York
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Steve -

    What is the purpose, in the time worked calculation, of adding the TRUE/FALSE condition to the calculation??

    Thanks - Mark

  11. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If by "TRUE/FALSE condition to the calculation" you are referring to the "(C5<B5)" part of the equation, this is used to add 1 (day) to the calculation if the end time is after the start time. For example in the 3rd col of Friday you started at 21:00 and ended at 01:00. The difference is a negative number, since the 01:00 is the day after so we must add 1 day to the calculation. A boolean calc like (c5<B5) will be TRUE or FALSE. FALSE Acts like a zero and a true acts a 1 in calculations.

    Steve

Posting Permissions

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