Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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% |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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% |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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% |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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% |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|