Need Help
 

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

<BExample 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%

Need Help
 

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.

--- "CG" wrote:
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

<BExample 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 Tot hrs 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%

Need Help
 

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

"Bob Bridges" wrote:

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.

--- "CG" wrote:
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

<BExample 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 Tot hrs 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%

Need Help
 

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 and we'll toss it
back and forth a bit -- more convenient than this forum.

--- "CG" wrote:
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

--- "Bob Bridges" wrote:
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.

--- "CG" wrote:
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

<BExample 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 Tot hrs 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%

Need Help
 

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.

"Bob Bridges" wrote:

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 and we'll toss it
back and forth a bit -- more convenient than this forum.

--- "CG" wrote:
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

--- "Bob Bridges" wrote:
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.

--- "CG" wrote:
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

<BExample 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 Tot hrs 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%

Need Help
 

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

--- "CG" wrote:
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.

--- "Bob Bridges" wrote:
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 and we'll toss it
back and forth a bit -- more convenient than this forum.

--- "CG" wrote:
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

--- "Bob Bridges" wrote:
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.

--- "CG" wrote:
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

<BExample 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 Tot hrs 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%