Need Help

C

CG

I need some assistance trying to calculate an IFTEXT (i think).
I have a schedule of multiple staff members. Each cell contain an inital of
the the person working at that time. Column A respresents the time in
fifteen minute increments. This time will start at 7:45am-9:00pm As row b1
represents Mon-Sun.
I am trying to automatically calculate the number of hours each staff worked
on the desk, which will be compared to the number of hours worked in a week,
which will in turn generate a percentage of on desk time.
Mon Tues Wed Thrus Friday
8:00AM CLOSE CLOSE JF JF DB
8:15AM MR MR JF JF DB
8:30AM MR TV CG JF DB
8:45AM JF TV CG JF DB
9:00AM JF JF JF JF DB

<B>Example i need the "On Circ" cells automatically calulated from the above
schedule to generate the number of circ hours per individual/<B>
Percentage of Circ Duty based upon 35 hours per week

Name Total of hours On Circ Off Circ Percent on circ

TV 35 35 0.00%
DB 35 11 24 31.43%
MR 35 4 31 11.43%
CG 35 4 31 11.43%
 
B

Bob Bridges

I may have misunderstood, but I don't think you need a program for this at
all. Let's say the initials are in B2:F54 (that covers 0745 to 2100); in the
On-circ column you should be able to use COUNTIF($B$2:$F$54, A<thisrow>);
it'll look at col A of the current row to get, say, "TV", and count up how
many times "TV" appears in B2:F54. That's the number of quarter hours TV was
On circ, so if you want it in hours you just use "=COUNTIF($B$2:$F$54,
A<thisrow>)/4". The Off-circ value is of course B<row> - C<row>, and the
percentage is C<row>/B<row>.
 
C

CG

Then where do i insert the "TV" or "CG" or "JF" etc...... I need a count of
each indivudals time....
I am not sure where to place the initials. I do belive you are getting the
gest of what i am doing. Maybe i am not understaning you. Please help on
more time. thanks so much
 
B

Bob Bridges

Oh, you still have the two areas you planned and described below, placed
either in the same sheet or in two different sheets, whichever you prefer.
I'm just saying that I don't think you need to write a VBA program to do
this; you could have posted this question in the Excel Worksheet Functions
forum for a COUNTIF should do the job for you just fine.

If you're really and truly new and Excel and aren't sure how to set up your
spreadsheet at all, contact me at (e-mail address removed) and we'll toss it
back and forth a bit -- more convenient than this forum.
 
C

CG

Ok thank you, i am pretty familiar with excel just the formulas really throw
me for a loop. I think i have the concept, i just cannot follow through to
end with a value. I have the spreadsheet ready if you will extend your
expertise. I would really appreciate it. Thanks so much.
 
B

Bob Bridges

Sure, you're welcome...except I don't see an email yet. Don't wait politely,
just send it already. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top