Timesheets - Tracking Client Time

[ultra_xcyter]

I am trying to keep track of how much time my client uses per month.
They have purchased 10 hours of consulting time per month and I need to
keep track of their usage. I am trying to setup a spreadsheet where I
can enter the start time and end time for various projects that I do
for the client, and have this time automatically subtracted from the
total time, which is 10 hours per month. How can this be done? Thank
you in advance for your tips.

 

[Guest]

I'll assume you have a very small group of clients. So you can set everything
on one worksheet.

First establish a table of customers and the time they start with each month
on the left side of the worksheet. For the sake of example I will start this
table in column G. In column G put the client name, and in column H put the
number of hours. Call the range "Timeblock" (or whatever).

To the right in the spreadsheet set up something like the following for your
daily transactions.
Col A - Client
Col B - Date
Col C - Start
Col D - End
Col E - Time

Col E is equal to Col D - Col C.

Enter in one transaction per line.

In cell Ix enter in vlookup (Gx,Timeblock,2) - sumif (A:A,Gx,E:E) where x is
the row and duplicate this formula for each client in column G. Column I will
track time remaining.

You can also use column G to validate the names entered in column A in a
dropdown list.

 

[ultra_xcyter]

Thank you for your reply and solution to my post. I greatly appreciate
it.

 

[ultra_xcyter]

Hi Dan,

Thank you for replying to my post. I greatly appreciate it. However, I
am a little confused on a couple things (I am a novice). I'm not sure
how this is to be set up in a worksheet. You mention that column G and
Column H would contain the client name and number of hours, and then
you instruct me to fill in values for columns A-E to the right of
columns G and H. Did you mean to the left? Also, what goes in column F?
Is that blank? And am I right to assume that the formula for column E is
=col D- col C?

One last question I have is, I am relatively new to writing formulas in
Excel and am not familiar with the 'vlookup' formula and in what cell I
am to enter this formula. I am also not too familiar with drop-downs
and validation.

Is there an example you can send that would give me a good visual of
what you are describing?

I'm sorry to be so slow at picking this up, but I am learning. Thanks
again for your help.

 

[ultra_xcyter]

I am attaching the spreadsheet I am working on to track client time
usage. Can someone take a look at it and tell me what I need to do in
order to track how many hours are left? As you will see, I want to be
able to track the remaining time by subtracting the total time used
from the contracted hours.


+-------------------------------------------------------------------+
|Filename: excel_timetracking_example.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4705 |
+-------------------------------------------------------------------+