# need to track when 16 hours of work are reached within any 24 hour period

#### bjonsson

##### New Member
I need to create a rolling log of sorts. What i need to track is when an employee has reached 16 hours time worked within the last 24 hours period. The 16 hours worked is not always continious. I need something that they can keep a log of, and when they reach 16 hours, have buzzers and alarms go off, lol. preferably warning once 14 and 15 hours are reached also. Pretty new to excel but any help would be greatly appreciated.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Justinian

##### Well-known Member
I need to create a rolling log of sorts. What i need to track is when an employee has reached 16 hours time worked within the last 24 hours period. The 16 hours worked is not always continious. I need something that they can keep a log of, and when they reach 16 hours, have buzzers and alarms go off, lol. preferably warning once 14 and 15 hours are reached also. Pretty new to excel but any help would be greatly appreciated.

You track the hours worked by employee ABC in cell A2, for example. So C2 would say 16, for the hours worked by that employee. In cell D2, you would use conditional formatting as follows:

=C2>15

Also, in cell D2, use this formula:

=IF(C2>15,"Warning - 16 hour mark reached!","")

#### bjonsson

##### New Member

You track the hours worked by employee ABC in cell A2, for example. So C2 would say 16, for the hours worked by that employee. In cell D2, you would use conditional formatting as follows:

=C2>15

Also, in cell D2, use this formula:

=IF(C2>15,"Warning - 16 hour mark reached!","")

thanks.
For every hour entered, the 24 hour time frame would also need to move ahead. 16 max hours within ANY 24 hour period.

#### bjonsson

##### New Member
As of right now this is conseptual. The problem we have is our guys use a 24 hour paper timesheet starting at 00:00-24:00 for each day. what i need is for guys to be able to enter there hours and have it automatically calculate whether they have reached 16 hour within the last 24 hours. the 24 hour timeframe would start at the last time entry, and go back 24 hours from then. our problem is that with each day on a seperate time sheet we always have to work backwards from there last entry to see if 16 hours have been reached. hopefully if a rolling 24hr log could be created that counts hrs worked in the past 24 after each entry, they would have a warning of when they are about to reach 16 hours, even if some of those hours are from the previous day.

Hope some of this makes sense.

#### juz2gud

##### New Member
If u dont need to make it complicated with a macro,, one solution can be:

Every employee keeps an excel sheet that has 3 columns

column A is time in
and column b is time out

Everytime he starts work he hits ctrl+shft+: (control + shift + colon [im sorry for possible spelling mistakes]) this will put the system time in the cell.. (for instance cell A1

when he leaves or takes break, he can hit same keys in column B in corresponding cell (for instance in this case cell B1)
column C is simply column B-column A. n gives u the number of hours
You can keep a sum command at cell c100 tht sums up the hours and notifies u when 14 or 15 or 16 hours hav been reached through conditional formatting...

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,316
Messages
5,836,600
Members
430,441
Latest member
SurendraTantia

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back