Urgent!! Can't get it work!

[crafty]

Hi there

I have a spreadsheet which I just couldn't figure out what is wrong with the formula.

The following arguments need to be in the formula.

• I want excel to show me (in row X) how long it took to issue the order. Beginning from the day, the order was created.
• If the order was on credit hold, then it should count from the date the order was released released (crow M) until the order was issued (row P). I also want it to count by workingdays only!
• Anther thing ist, that if the order was placed before noon, then it should count from the first day on, otherwise it should start counting from the next day. This is why I separated day and time.

This is how I tried it:
=IF($M5<>"",(IF($I5<$V$1,NETWORKDAYS($H5,$U5),NETWORKDAYS($H5+1,$U5))),(IF($O5>$V$1,NETWORKDAYS($N5,$U5),NETWORKDAYS($N5+1,$U5))))

But I didn't even get it to distinguish between the times

[/IMG]

This drives me nuts Thanks for your help!

 

[GorD]

Hopefully this should do it, I haven't used the additional columns splitting date and time

Excel 2007
	G	H	I	J
1	Order Create	Credit Release	Goods Issue	CALC
2	03/01/2011 08:29	06/01/2011 09:10	14/01/2011 15:58	7
3	03/01/2011 08:34		04/01/2011 15:45	2
4	03/01/2011 14:53		05/01/2011 16:04	2
5	04/01/2011 13:53
Sheet1

Cell Formulas
Range	Formula
J2	=IF(HOUR(MAX(G2,H2))<12,NETWORKDAYS(MAX(H2,G2),I2),NETWORKDAYS(MAX(H2,G2)+1,I2))
J3	=IF(HOUR(MAX(G3,H3))<12,NETWORKDAYS(MAX(H3,G3),I3),NETWORKDAYS(MAX(H3,G3)+1,I3))
J4	=IF(HOUR(MAX(G4,H4))<12,NETWORKDAYS(MAX(H4,G4),I4),NETWORKDAYS(MAX(H4,G4)+1,I4))

 

[crafty]

Thanks for your quick help GorD.

I adapted the formula and it seems to work. But this one got me confused. Is this due to the time which isn out of the ordinary? How does he come up with a difference of (negative) two days?

 

[GorD]

It's causing an error in the calc because the start date is becoming > the complete date (as we are adding a +1 as its after12pm) - net work days needs the smaller number to be first

 

[GorD]

You could wrap the whole thing in another max statement with a one, so any value less than 1 would revert to 1

 

[crafty]

well, with another If-argument you mean? This is what I tried, but it wouldn't work. The red bolded part is the original formula which is absolutely correct.

=IF(MAX(IF(HOUR(MAX(G7834,J7834))<12,NETWORKDAYS(MAX(J7834,G7834),M7834),NETWORKDAYS(MAX(J7834,G7834)+1,M7834)))<0,"1",IF(HOUR(MAX(G7834,J7834))<12,NETWORKDAYS(MAX(J7834,G7834),M7834),NETWORKDAYS(MAX(J7834,G7834)+1,M7834)))

 

[GorD]

more like

=max(1,IF(HOUR(MAX(G2,H2))<12,NETWORKDAYS(MAX(H2,G2),I2),NETWORKDAYS(MAX(H2,G2)+1,I2)))

 

[crafty]

Okay thanks for that hint also! Got it worked out so far and it seems to work and to pull the right data. It starts driving me nuts again tough. I made him give me the number of total days the orders were proceeded, less than 5, than 10 and then all else greater than 10. There but the total didn't match the overall total of orders and there was a slight difference of 89 pcs.
I now set a filter and found out, that he wouldn't put exactly 89 pcs. in order, when I sort the orders by the time they were proceeded. To me it seems, that he ignores them. Does anybody have any idea, what the problem could be?

Thanks!

 

[crafty]

[TABLE="width: 500"]
<tbody>[TR]
[TD]
Would really appreciate if anybody would take a look into this big thing. I really can't figure it out!

This is part of the table I'm trying to set up. You'll find the deviating numbers on the top left and right.
[TABLE="class: grid, width: 1260"]
<tbody>[TR]
[TD][/TD]
[TD]G
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD="colspan: 4"]V | W | X | Y |
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]by quantity
[/TD]
[TD]by %
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Lead time whithin
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]total of orders
[/TD]
[TD="align: right"]10896
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<5 days
[/TD]
[TD]<10 days
[/TD]
[TD]>10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]overrun delivery date
[/TD]
[TD="align: right"]2970
[/TD]
[TD]27.26%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7681
[/TD]
[TD="align: right"]2277
[/TD]
[TD="align: right"]849
[/TD]
[TD="align: right"]10807
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]on credit release
[/TD]
[TD="align: right"]2497
[/TD]
[TD]22.92%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Orders overrun delivery day by
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<5 days
[/TD]
[TD]<10 days
[/TD]
[TD]>10 days
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2352
[/TD]
[TD="align: right"]385
[/TD]
[TD="align: right"]233
[/TD]
[TD="align: right"]2970
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Orders on credit block
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<5 days
[/TD]
[TD]<10 days
[/TD]
[TD]>10 days
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1446
[/TD]
[TD="align: right"]74
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]1559
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Order_create
[/TD]
[TD]Credit_block
[/TD]
[TD]Credit_release
[/TD]
[TD]Req_delivery
[/TD]
[TD]Goods_issue
[/TD]
[TD]Total days proceeded
[/TD]
[TD]On credit block
[/TD]
[TD]Days overrun
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14
[/TD]
[TD="align: right"]4/6/11 12:56 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4/7/2011
[/TD]
[TD="align: right"]4/6/11 7:23 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15
[/TD]
[TD="align: right"]5/3/11 3:29 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/3/2011
[/TD]
[TD="align: right"]5/3/11 4:04 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16
[/TD]
[TD="align: right"]6/22/11 12:19 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6/22/2011
[/TD]
[TD="align: right"]6/22/11 2:14 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17
[/TD]
[TD="align: right"]8/12/11 2:34 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8/12/2011
[/TD]
[TD="align: right"]8/12/11 3:32 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18
[/TD]
[TD="align: right"]2/23/11 2:34 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/24/2011
[/TD]
[TD="align: right"]2/23/11 4:25 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19
[/TD]
[TD="align: right"]5/20/11 2:26 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/20/2011
[/TD]
[TD="align: right"]5/20/11 7:04 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20
[/TD]
[TD="align: right"]9/6/11 3:27 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9/12/2011
[/TD]
[TD="align: right"]9/6/11 6:32 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21
[/TD]
[TD="align: right"]10/14/11 3:31 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/20/2011
[/TD]
[TD="align: right"]10/14/11 4:43 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22
[/TD]
[TD="align: right"]10/4/11 12:17 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/5/2011
[/TD]
[TD="align: right"]10/4/11 7:16 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23
[/TD]
[TD="align: right"]12/21/11 3:25 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12/21/2011
[/TD]
[TD="align: right"]12/21/11 6:04 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24
[/TD]
[TD="align: right"]8/26/11 5:47 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9/2/2011
[/TD]
[TD="align: right"]8/26/11 6:34 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25
[/TD]
[TD="align: right"]9/16/11 2:34 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9/26/2011
[/TD]
[TD="align: right"]9/16/11 5:39 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26
[/TD]
[TD="align: right"]2/22/11 10:37 AM
[/TD]
[TD="align: right"]2/22/11 10:37 AM
[/TD]
[TD="align: right"]2/22/11 10:55 AM
[/TD]
[TD="align: right"]2/28/2011
[/TD]
[TD][/TD]
[TD]371
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27
[/TD]
[TD="align: right"]2/3/11 12:11 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/3/2011
[/TD]
[TD="align: right"]2/3/11 1:19 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28
[/TD]
[TD="align: right"]5/12/11 12:42 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/13/2011
[/TD]
[TD="align: right"]5/12/11 5:42 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formulas used as follows:
I2: =COUNTA($R14:$R30000)
I3: =COUNT($T14:$T30011)
I4: =COUNT($J14:$J30011)

V3: =COUNTIF($R14:$R30000,"<=5")
W3: =(COUNTIF($R14:$R30000,"<=10"))-V3
X3: =COUNTIF($R14:$R30007,">10")
Y3: =SUM(V3:X3)

V7: =COUNTIF($T14:$T30011,"<=5")
W7: =(COUNTIF($T14:$T30011,"<=10"))-$V$7
X7: =(COUNTIF($T14:$T30011,">10"))
Y7: =SUM(V7:X7)

Same for the credit one.


Then for the matrix itself as follows:

R14: =IF((IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14))))))<=0,"1",(IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14)))))))
S14: =IF((IF(I14<>"",(IF(HOUR(MAX(I14,J14))<12,NETWORKDAYS(MAX(I14),J14),NETWORKDAYS(MAX(I14)+1,J14))),""))<=0,"1",(IF(I14<>"",(IF(HOUR(MAX(I14,J14))<12,NETWORKDAYS(MAX(I14),J14),NETWORKDAYS(MAX(I14)+1,J14))),"")))
T14: =IF(($L14+1)>$M14,"",(NETWORKDAYS($L14,$M14)))


Thanks so much! [/TD]
[/TR]
</tbody>[/TABLE]

 

[crafty]

Got it worked out properly. It was pretty much just a string around a part of the formula in column R which messed it up.

See http://www.mrexcel.com/forum/showthread.php?649561-Excel-ignores-formated-rows