• 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.

Cumulative dates with networking days formula

D

Dave_P

New Member
Hi Friends,


I'm just starting out with excel and i have researched a formula but it doesn't seem accurate its oftern a day or two out.


what i wanted to achieve was the time it takes to deliver a product from th eday the quotation took place. so i have 5 cells that have data that will show how many working days till the order was fulfilled.


the formula is below:


=IF(OR(A2="",B2=""),0,NETWORKDAYS(A2,B2)-SIGN(NETWORKDAYS(A2,B2))+IF(OR(B2="",C2=""),0,NETWORKDAYS(B2,C2)-SIGN(NETWORKDAYS(B2,C2))+IF(OR(C2="",D2=""),0,NETWORKDAYS(C2,D2)-SIGN(NETWORKDAYS(C2,D2))+IF(OR(D2="",E2=""),0,NETWORKDAYS(D2,E2)-SIGN(NETWORKDAYS(D2,E2))+IF(OR(E2="",F2=""),0,NETWORKDAYS(E2,F2)-SIGN(NETWORKDAYS(E2,F2))+IF(OR(F2="",G2=""),0,NETWORKDAYS(F2,G2)-SIGN(NETWORKDAYS(F2,G2))))))))


it seems to work but the actual amount of days is incorrect when double checked. any help would be greatly appreciated. thank you
 
Hi Dave ,


I suggest that instead of asking others to debug your formula , you mention exactly how your data is laid out , and what you want to achieve.


There may be many ways to achieve the same result , and focusing on only troubleshooting your formula may come in the way of getting the right solution.


Can you say exactly what data is present in cells A2 through G2 ? What should be the output , and what are the conditions which will generate the output ?


Narayan
 
Hi Narayan,


thank you for your patience, this is new to me.


there will be 1 - 7 cells with dates inputed into them.


what i wanted was one cell that calculated the time in days between the dates and gave a result of "x" days from start to end.so as the job progressed the total days would increase.


cell 1 contact date

cell 2 sent for quote

cell 3 quote sent to factory

cell 4 quote recieved from factory

cell 5 quote sent to rep

cell 6 job bag sent to head office

cell 7 job bag sent to factory


cell 8 total days from start to end


cell 9 average days across all jobs


i'm hoping this would help to increase efficiency when we look at how long it takes.


i hope i have explained myself correctly. kind regards
 
Hi Dave ,


If I understand you correctly , the cells you have mentioned can be taken as A2 through G2 , at least as an example.


H2 will contain the difference between the latest date and the first date e.g. if cells A2 , B2 and C2 have dates in them , and D2 through G2 are blank , then H2 will contain C2 - A2 ; when D2 is filled in with a date , H2 will become D2 - A2.


Similarly , I2 will contain the average of all filled in cells ; to take the same example as above , when A2 through C2 are filled in , I2 will contain the average of these three i.e. (C2 - A2)/3 ; when D2 is also filled in , I2 will become (D2 - A2)/4.


Lastly , you are talking of networking days ; does this mean when subtracting two dates to find the duration between them , we have to consider only working days ( by using the NETWORKDAYS function ) , and not all days ?


Is this correct ?


Narayan
 
Hi Narayan,


Yes your correct on all of your points. I would like to exclude weekends, sat and sun and public holidays where possible.


thank you
 
Hi Dave ,


In H2 , you can put in the formula : =NETWORKDAYS($A2,MAX($A2:$G2),$K$2:$K$16) , and in I2 , the formula : =$H2/COUNT($A2:$G2).


I have assumed that the dates will be in ascending order , so that the MAX function will return the latest date , which will also be the last date.


A list of public holidays is assumed to be in the range K2:K16


Narayan
 
thank you narayan, i sincerely appreciate your help.


I'll do some research on your formula to understand it better, that said i would really like to learn more about excel and look to do a course with chandoo!


where would you suggest a good place to start?


kind regards dave
 
Hi Dave ,


I think Chandoo's website is a good place to start.


You will find a lot of articles of various aspects of Excel , such as formulae , pivot tables , charting , data analysis , macro programming ....


And the forum is always available to answer any question.


Other good websites are http://www.mrexcel.com/ , http://www.myonlinetraininghub.com/ , http://www.databison.com/ , http://www.datapigtechnologies.com/ ; in fact there are so many that you are spoilt for choice !


Narayan
 
thank you Narayan. will have a look around.


one more question, formula for I2 is great.


how would i do the average for g2: g16 etc etc
 
i tried =AVERAGE(Table2[DAYS TO PROD])


which gives a false result. i would like a cumalitive result as the jobs increase. meaning as the rows fill.


wow this hard to explain!
 
Hi Dave ,


What is the meaning of a cumulative result ?


One row pertains to what ?


Can you copy + paste several rows of data , and give the first two or three results for the average ?


Narayan
 
RFQ IN RFQ OUT QUOTE In QUOTE REP JB IN JB OUT DAYS TO PRODUCTION

5/07/13 5/08/13 5/09/13 10/10/13 11/11/13 12/12/13 115

5/07/13 5/08/13 10/09/13 1/12/13 106

5/07/13 1


HI Narayan,


as above, so i wanted the average to calculate as row four was added and so on based on the information in the "days to production" column. this would be above my table.


so now i'n not sure if the result would be accurate. so i guess we could only have a result once the data has been filled in "rfq in" and "jb out"....the idea was that the employee would try to reduce the number in the box by pushing factories for quotes.


thank you
 
Hi Dave ,


The point is , is one row for one RFQ ? Would the RFQ in row 3 be a different one from the RFQ in row 2 ?


If it is , then what would a cumulative average mean ?


It would probably be better if you had a cumulative ( overall ) average for each stage , so that it would immediately highlight the bottleneck area i.e. if the overall average days for stage 2 are 3 days , while the overall average days for stage 5 are 17 , then it would be beneficial to try and bring down the 17 to 15 , than to try and bring down the 3 to 1.


Narayan
 
Hi Narayan,


okay, that seems great. but i have no idea how i would do that? lol.


that really is drilling down the info.


I was going for a very simple idea. just as the data was completed in the last cell.


so if cells 1 -7 had data then just average the number in "days to production"


i like your idea i just don't have the capacity to figure that out...
 
Hi Dave ,


I can upload a workbook which has the formulae ; which website can you download from ? DropBox , Google Docs , SpeedyShare , SkyDrive ?


Narayan
 
You must log in or register to reply here.
Back
Top