Tracking Request Hours and Placing Requests into a bucket based on # of hours used

Frank S.

First, I appreciate all the help and suggestions, since I'm struggling with how to accomplish this task.

I have a sheet that contains requests,(assigned a unique number) as requests are approved the # of hours is captured for each request.

Each billable bucket is capped at 1800 hours and requests can spill over from one bucket to another (i.e. request 1 is 2200 hours, would fill bucket 1 and 400 hours would go into bucket 2)

Here is what I'm trying to do:

1. When a request is approved know what bucket it is going to be billed in based on the hours remaining in the current bucket. Would ideally update the original request sheet.
2. Maintain a log of all billable buckets and show the request number associated with that bucket and the number of hours used. If a request fills a bucket the remaining hours go into the next bucket.
3. Be able to show the current bucket and the number of hours remaining in that bucket.

I'm open to adding any additional sheets as needed, including add helper columns to my original sheet.

Mark Cronk

Hi Frank,

Do you really need to move his into buckets or can you just divide the total by 1800 and know how many buckets are full and how much is in the next bucket? E.g. if you've used 4,900 hours you've filled 2.72 buckets - 2 buckets are full and you have 1,296 hrs in the 3rd.

Mark