Time Adherence Sheet for Excel

[Swade730]

Hello guys I am new to the forum and I am hoping I can get some questions answered.

So I work in a call center in which they monitor the times that we are not available to take phone calls.

Out of an 8 hour day, We are granted 2 hours to be off of the phone for research.

2 hours of unavailability in an 8 hour day puts a worker at 75% adherence (which is the requirement)

I started playing around in excel to see if I can create a spread sheet to help keep track of the allotted 2 hours I have used, throughout my work day.


A B C D E
Start End Elapsed Time Total time
1 ACW 8:30 9:00 0:30
2 ACW 9:10 9:25 0:15 0:30
3 ACW 9:30 9:45 0:15 0:15



ACW stands for after call work or simply The time that is being used from the 2 hours in unavailable.

Total time is reflected in column E.

Here are the two problems I am facing...

I want column F to add the time as new start and end times are entered in each row.
for example (total time in cell E 3 should read 45 minutes)

And How do I make column G reflect the percentage of adherence? obviously it would start at 100% and the closer to two hours being used down the rows will bring that number closer to 75% at the end of the work day.

I would really appreciate any feedback on this dilema. Thank you so much.

[SamT]

Try this workbook:
How it works:
There is a Selection Change Macro in the Sheet's CodePage.
It check if you selected more than one cell. If Yes, it does nothing.
It checks to see if you clicked a cell in Columns B or C. If not then it does nothing.
It checks if the one cell selected has a value, If Yes, it does nothing.
If all the tests pass, it puts the NOW time in The cell slected.

The rest is just add and subtract formulas with a check to see if Column C, that Row, is empty. And some special formatting.

In some of the Formulas you will see (2/24) that is to convert to Windows' Serial time. All the Cells and formulas use Windows' Serial Time.

To use it, just click the top yellow cell in B to set a start tiem. Click in the same row in C to set the Stop time.

To clear a cell in B or C, Right Click it and choose Clear Contents. Do not Clear any of the other columns' cells.

This will work down to Row 40.

[haul[s]

nice