• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to determine the number of days worked in order to calculate a daily average

W

Wintana

New Member
Hello,

I keep track of a production spreadsheet for my team and I am trying to figure out how to calculate their daily average production. I'm new to excel but I was able to get some really helpful formulas from the posts in the forums (Thanks everyone!) but it looks like I hit a brick wall. I am trying to calculate the number of days a person has worked within a certain period of time. I was able to count the number of jobs a person has done within the specified period of time but I would like to extract the count of unique dates within the specified period of time. For example, Alan was able to complete 4 jobs in the specified time period. I have 3 dates of 7/19/2013 and 1 date of 7/18/2013. I would like the formula to count 2 distinct dates instead of 4 dates (I hope I'm making sense). Any help would be appredciated.

Thank you.
 

Attachments

Hello,
Welcome to the forum.

One approach is using the following array formula:
=SUMIF($A$13:$A$17,$E13,$B$13:$B$17)/SUM(SIGN(FREQUENCY(IF(FinalizedBy=$E13,FinalizedDates), IF(FinalizedBy=$E13,FinalizedDates))))

enter with Ctrl + Shift + Enter

See attached workbook.

Cheers,
Sajan.
 

Attachments

Hello Sajan,

Thanks for the quick reply.
I looked at the attached workbook and the formulas work great but when I added them to my original spreadsheet I realized that you used the name column from the number of jobs table (I think).
This is the formula that I am using for counting the number of jobs (formula is in workbook):

=COUNTIFS($B$2:$B$10,">="&$E$2,$B$2:$B$10,"<="&$F$2,$A$2:$A$10,"SEA")

Would it be possible to rewrite your formula by using the names from the Finalized by/Finalized on table? I tried replacing the range by I got an error. Sorry if I didn't specify.

Thanks for all your help,
Wintana
 
Hello Wintana, if i undestood you correcly, you need the formulas that are in the total jobs table to be conected with the names in the ffinalized by table right?
see if the attached file is close to what you need
 

Attachments

Hi Paulo,

Thanks for helping.
I'm trying to find the number of days that a person worked during a specified period of time. I would like to extract the count of unique dates within the specified period of time. For example, Alan was able to complete 4 jobs in the specified time period. I have 3 dates of 7/19/2013 and 1 date of 7/18/2013. I would like the formula to count 2 distinct dates instead of 4 dates. I see from your spreadsheet that the numbers in the total jobs table have been mixed up so your results are a little off. But I did try and check the second part of the formula in cell F13 (=SUM(SIGN(FREQUENCY(IF(FinalizedBy=$E13,FinalizedDates), IF(FinalizedBy=$E13,FinalizedDates))))) and the result is 4. I need it to show 2 which is the number of days that Alan worked. Makes sense?
 
Hello again Wintana,

i made some changes to the file, please test if it is working properly, i think a understood your problem.

if it is not quite what you need, i am glad to try an help again :)
 

Attachments

Paulo,

Looked at the spreadsheet and I see that you made quite a few changes. However the results are still off. Can you explain to me what you are trying to do. Why did you add these extra columns next to the Finalized by/Finalized on table?

SEA41477 1
James41473 2
Alan41474 3
Alan41474 3
Alan41473 1
James41473 2
Alan41474 3
Kimmy41479 1
Ling41479 1


Thanks
 
Hello,

i made the following changes:
  • In column C, i used the formula ="A2&B2", the result guives the name of worker, with the date in numeric value, this is a quick way of finding repeated days (for example Alan41474 apears every time Alan Finalized a work in the date 18-07-2013
  • In column D, the formula "=COUNTIFS($C$2:$C$10;C2)", shows the number of works where finalized in the same day by the same worker
  • In total Jobs the formula =COUNTIFS(FinalizedBy;A13;FinalizedDates;">="&$F$2;FinalizedDates;"<="&$G$2) shows the number of jobs finished
  • in the dayly average, =(COUNTIFS(FinalizedBy;A13;FinalizedDates;">="&$F$2;FinalizedDates;"<="&$G$2))/(IF(COUNTIFS(FinalizedBy;F13;$D$2:$D$10;">1");1+COUNTIFS(FinalizedBy;F13;$D$2:$D$10;"=1");COUNTIFS(FinalizedBy;A13;$D$2:$D$10;"=1"))) shows (hopefully) the information you need)
I rearranged the sheet so that is is more easy to read, check again please :)
(sorry for the mess :))
 

Attachments

Hi there,

Thank you for the breakdown. It really helped. It does seem to work perfectly with my sample worksheet but when I tried to incorporated to my original spreadsheet I realized that I would have to add an extra 2 columns to my spreadsheet. Is there a way to do it without using the columns that you add in the spreadsheet? Sorry if I seem demanding.

Thank you for all your help.
 
I will think in this problem, but meanwhile, can you add the 2 extra columns after your data? (in the end of the sheet?) i tried to make the same calculations without the support columns but it isn't so simple :(, i will try get back to you with answers but its gonna be hard :)
 
Paulo,

I have attached the headers from my original spreadsheet. As you will see I have several columns that have names and dates. I had only given you one section for the sample worksheet. I would have to create 2 more columns for every color coded section. I'm trying to avoid that if possible (the spreadsheet is large enough already :)). I do appreciate all your help though.
Thank you.
 

Attachments

Hello Wintana,
Wintana said:
Hello Sajan,

Thanks for the quick reply.
I looked at the attached workbook and the formulas work great but when I added them to my original spreadsheet I realized that you used the name column from the number of jobs table (I think).
This is the formula that I am using for counting the number of jobs (formula is in workbook):

=COUNTIFS($B$2:$B$10,">="&$E$2,$B$2:$B$10,"<="&$F$2,$A$2:$A$10,"SEA")

Would it be possible to rewrite your formula by using the names from the Finalized by/Finalized on table? I tried replacing the range by I got an error. Sorry if I didn't specify.

Thanks for all your help,
Wintana
Click to expand...

Instead of the named references, you can use actual cell references:
The array formula for cell F13 is
=SUMIF($A$13:$A$17,$E13,$B$13:$B$17)/SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$E13,$B$2:$B$10), IF($A$2:$A$10=$E13,$B$2:$B$10))))

enter with Ctrl + Shift + Enter

Attached is the workbook that shows the same.

Cheers,
Sajan.
 

Attachments

Sajan,

Ok, got it. One last question and I think that's it. I'm using your formula but I've modified slightly to use the dates in cells E2 and F2 instead of using the Finalized on cell range. So the formula would look something like this:

=SUMIF($A$13:$A$17,$E13,$B$13:$B$17)/SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$E13,$B$2:$B$10,">="&$E$2), IF($A$2:$A$10=$E13,$B$2:$B$10,"<="&$F$2)))) entered with Ctrl + Shift + Enter

I keep getting an error. Could you tell me what I'm doing wrong?

Thank you,
Wintana
 
Hello Wintana,
The highlighted segment of your formula would not work since it is returning a string instead of FALSE as in the original formula.
=SUMIF($A$13:$A$17,$E13,$B$13:$B$17)/SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$E13,$B$2:$B$10,">="&$E$2), IF($A$2:$A$10=$E13,$B$2:$B$10,"<="&$F$2))))

When you say you want to use the dates in E2 and F2, can you elaborate on what you are looking to do?

I would encourage you to upload a sample file with descriptions to clarify what you are after.

-Sajan.
 
Ok. Here goes. I have attached the original spreadsheet that I am working with. I'm trying to keep track of the individual production of my team members. I have figured out how to count the number of jobs per month per person per task. Now I need a formula that will give a daily average per person per task. The problem problem I am running into is that people don't work all the working days of the month (sick leave, time off, etc), so I only want the days that they actually worked. For example, there are 24 working days in the month of September (we work mon-sat), but Alan only worked 3 days in the FINALIZE task. so I want to divide his production for that particular task by the number of days that he actually worked on the task. So that would be 7 jobs divided by 3 days. So I guess the formula that I am looking for has to count the unique dates within a date range (9/1/2013 - 9/30/2013) for a specific team member.
When I apply your formula to my spreadsheet, your formula uses the cells from a cell range from the Tracking Log (i.e. I2:I711) , I think. I would like it to use the cells with the date on Sheet 1(i.e. cells B4 and B5) if possible. Let me know if I need to clarify further.
 

Attachments

Hello Wintana,
A few things to note to make your life easier...

Relative references versus Absolute references
Read a post by Hui on cell references. That should help save you time when copying formulas from one cell to another. (This is a comment on your formulas on Sheet1 column D, where you reference A3:A12 repeatedly. If you changed thta to $A$3:$A$12, the reference will remain the same even when you copy the formula down to additional cells. A better approach might be to give the range A3:A12 a name such as "Holidays" and then use that name in your formulas. Makes it easier to read them.)

Whenever possible, use actual dates instead of text strings to specify date values. Excel gives you a lot of flexibility to format a date value like "Jan 4, 2014" or 1/4/2014 or "2014-January", etc. On the other hand, if you entered the date as a text string, you are stuck with what you entered. (This is in reference to the Month names you have in your header row in the "Monthly Summary" tab.)

Whenever possible, strive to make formulas generic so that you can copy the formula from one cell to another without having to edit them from one cell to another, one row to another, one column to another, etc.
(For example, in the Monthly Summary tab, you have different references in each column that you probably keyed in manually.)

On Sheet1, in Working Days column, did you mean to double count Fridays?
Your formula is
=NETWORKDAYS(B3,C3,A3:A12)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B3&":"&C3)))=6))
NETWORKDAYS function returns the number of working days (Mon-Fri), after discounting holidays. As such, if you add the portion SUMPRODUCT(..), you are effectively adding Fridays a second time.
Instead of 6 in the formula, you probably wanted 7 (which is for Saturdays), to get the working days as Mon=Sat.
=NETWORKDAYS(B3,C3,$A$3:$A$12)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B3&":"&C3)))=7))

If you have Excel 2010 or later, you can simplify this further as
=NETWORKDAYS.INTL(B3,C3,11,$A$3:$A$12)
(Here 11 indicates that weekend is comprised of Sundays only.)

I will continue in another post...
 
Continuing from previous post...

In the attached workbook, I have added a Named range called "TrackingLogData" that will grow (or shrink) as you add (or delete) rows. To simplify calculations, I have a marker on the last row of Tracking Log sheet. Insert new rows above that.

I also added a header column in the Tracking Log sheet. (Row #1). This header is used to determine which column to include in calculations on the Monthly Summary tab.

In the Monthly Summary tab, I added three new rows at the top to make it easy to reference in formulas in the remaining rows. The first four rows on that tab are auto-calculated based on the "StartDate" you set on Sheet1 (in cell F3). (Try it out by changing the StartDate to the 2014-2015 fiscal year.)

On the Monthly Summary tab, I added a column A that shows the names of your team members. This makes it easier to reference them in calculations. Column A is auto-calculated based on values in column B.

You can hide Column A, and rows #1-#3 if desired on the Monthly Summary tab.

I replaced most of your formulas on the Monthly Summary tab so that the same formula can be copied down and to the right.

To add a section for a new team member, simply copy an existing team member's section, and change the name. The rest of the formulas should auto-correct.

Please test some of the calculations manually to confirm that the results are what you expected.

(By the way, since I used NETWORKDAYS.INTL, I am assuming that you have Excel 2010 or later. If not, the working days calculation will need to be changed to NETWORKDAYS(..) + SUMPRODUCT(..) like you had before.)

Once you confirm things are working as you expect, we can look at optimizing stuff if needed.

Cheers,
Sajan.
 

Attachments

Hey Sajan,

Thank you very very much for all the input and info. I didn't mean for this to get so complicated and time consuming. :)
I have Excel 2007 so I'm going to keep the working days calculation that I had with the Friday changed to Saturday. I'll get back to you after I have tested it out.
 
Hi Wintana,
Thanks for the feedback! Happy to help! If you need help with any of the functions or formulas used, you know where to find us!

Cheers,
Sajan.
 
Hi Sajan,

I'm slowly going through the spreadsheet you sent me and I'm trying to use your formulas in a more updated workbook. Some of the results are not correct so I have a few questions. :)

When you said:
I also added a header column in the Tracking Log sheet. (Row #1). This header is used to determine which column to include in calculations on the Monthly Summary tab.
Click to expand...

Is there anything I have to do to create the header column or did you just type in the names?




In the attached workbook, I have added a Named range called "TrackingLogData" that will grow (or shrink) as you add (or delete) rows. To simplify calculations, I have a marker on the last row of Tracking Log sheet. Insert new rows above that.
Click to expand...

Can you tell me what you did to name the range and to create the marker?


Thanks,
Wintana
 
Hi Wintana,
The first segment of the header row on the Tracking Log sheet is meant to match the data on the Monthly Summary tab. In the formulas on Monthly Summary tab, the match is by, for example, "Finalize" & " By" or "Finalize" & " Date", where the word Finalize is data you have in the second column on Monthly Summary.

To create the TrackingLogData, I added a cell value "LastRow" at the very bottom of the Tracking Log sheet. The TrackingLogData then looks at all data up to that row.

-Sajan.
 
Hi Wintana,
Also, it may be easier to replace the data in the file I uploaded with your new data on the Tracking Log sheet, instead of trying to repair the formulas. The formulas on the Monthly Summary tab should adapt to the new data automatically.

Cheers,
Sajan.
 
Sajan,

So if I copy over the Monthly Summary and Sheet 1 tabs as they are, they should adapt, right?
I'm just adding the headers to the Tracking Log?
 
I would suggest doing it the other way around... copy just the data from your new workbook's Tracking Log sheet into the old workbook's Tracking Log sheet. (i.e. not the whole tab, but just the data.)
 
You must log in or register to reply here.
Back
Top