Tracking work hours

[ozcee]

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.
I’’m thinking something like
Now() – countif
But not sure how to put it together.
Thanks in advance.

[sdckapr]

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

[ozcee]

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

[sdckapr]

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

[sdckapr]

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

[ozcee]

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

[TheRedBaron]

Steve -

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

Thanks - Mark

[sdckapr]

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