How many days late, within date ranges rolling back from today?

jfgoodhew1 · Oct 16, 2012

I have an order book, showing date customer requires in A, actual ship date in B. Late is defined as >2 working days late, and ABS(NETWORKDAYS) is used with IF to get "yes" or "no" in C for each shipment.
If "yes" in C, then number of days late is posted in D.

First, I want to use array formulae and such to learn how to use them. I don't have any macro experience or VBA, and learning pivot tables is next on my to-do list

What I need to know:
How many shipments went late in a given month (easy).
How many days in total, over all late shipments, things went late for each month (not easy).
Mean days late per late shipment per month (lemon squeezy).

Any help with the missing puzzle piece? Attachments not yet allowed for me, so LINK.

jfgoodhew1 · Oct 17, 2012

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Customer Date

XXX

Early/Late?

Days Early/Late

Year

Month

Total Shipments

Early/Late Shipments Per Month

Days Late

2

14/08/2010

16/08/2010

no

0

2012

10

XXX

1

3

19/11/2011

27/11/2011

XXX

3

2012

XXX

4

15/04/2012

19/04/2012

yes

2

2012

8

0

XXX

5

01/05/2012

03/05/2012

no

0

XXX

0

XXX

6

01/05/2012

04/05/2012

yes

2

XXX

6

0

7

01/10/2012

02/10/2012

no

0

XXX

5

2

1

8

XXX

18/10/2012

yes

XXX

2012

4

1

9

XXX

3

0

10

2012

2

0

XXX

11

2012

XXX

0

12

XXX

2011

12

0

XXX

13

XXX

2011

11

1

Sheet1

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; ">
<tr>
<td>Spreadsheet Formulas</td>
</tr>
<tr>
<td>
<table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;">
<tr style="background-color:#cacaca; font-size:10pt;">
<td>Cell</td>
<td>Formula</td>
</tr>
<tr>
<td>C2</td>
<td>=IF(B2>0,IF(-1*(NETWORKDAYS(A2,B2))>3,"yes",IF((NETWORKDAYS(A2,B2))>3,"yes","no")),"")</td>
</tr>
<tr>
<td>D2</td>
<td>=IF(C2="yes",ABS(NETWORKDAYS(A2,B2))-2,0)</td>
</tr>
<tr>
<td>E2</td>
<td>=YEAR(TODAY())</td>
</tr>
<tr>
<td>F2</td>
<td>=MONTH(TODAY())</td>
</tr>
<tr>
<td>G2</td>
<td>=IF($F2>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F2&$E2)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F2&$E2)))</td>
</tr>
<tr>
<td>H2</td>
<td>=IF($F2>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F2&$E2)*--(TEXT($B$2:$B$8,"myyyy")=$F2&$E2)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F2&$E2)+--(TEXT($B$2:$B$8,"myyyy")=$F2&$E2)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>C3</td>
<td>=IF(B3>0,IF(-1*(NETWORKDAYS(A3,B3))>3,"yes",IF((NETWORKDAYS(A3,B3))>3,"yes","no")),"")</td>
</tr>
<tr>
<td>D3</td>
<td>=IF(C3="yes",NETWORKDAYS(A3,B3)-2,0)</td>
</tr>
<tr>
<td>E3</td>
<td>=IF(AND(E2=YEAR(TODAY()),NOT(F2=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F3</td>
<td>=IF((F2-1)>=1,F2-1,F2+11)</td>
</tr>
<tr>
<td>G3</td>
<td>=IF($F3>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F3&$E3)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F3&$E3)))</td>
</tr>
<tr>
<td>H3</td>
<td>=IF($F3>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F3&$E3)*--(TEXT($B$2:$B$8,"myyyy")=$F3&$E3)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F3&$E3)+--(TEXT($B$2:$B$8,"myyyy")=$F3&$E3)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>C4</td>
<td>=IF(B4>0,IF(-1*(NETWORKDAYS(A4,B4))>3,"yes",IF((NETWORKDAYS(A4,B4))>3,"yes","no")),"")</td>
</tr>
<tr>
<td>D4</td>
<td>=IF(C4="yes",NETWORKDAYS(A4,B4)-2,0)</td>
</tr>
<tr>
<td>E4</td>
<td>=IF(AND(E3=YEAR(TODAY()),NOT(F3=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F4</td>
<td>=IF((F3-1)>=1,F3-1,F3+11)</td>
</tr>
<tr>
<td>G4</td>
<td>=IF($F4>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F4&$E4)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F4&$E4)))</td>
</tr>
<tr>
<td>H4</td>
<td>=IF($F4>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F4&$E4)*--(TEXT($B$2:$B$8,"myyyy")=$F4&$E4)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F4&$E4)+--(TEXT($B$2:$B$8,"myyyy")=$F4&$E4)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>C5</td>
<td>=IF(B5>0,IF(-1*(NETWORKDAYS(A5,B5))>3,"yes",IF((NETWORKDAYS(A5,B5))>3,"yes","no")),"")</td>
</tr>
<tr>
<td>D5</td>
<td>=IF(C5="yes",NETWORKDAYS(A5,B5)-2,0)</td>
</tr>
<tr>
<td>E5</td>
<td>=IF(AND(E4=YEAR(TODAY()),NOT(F4=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F5</td>
<td>=IF((F4-1)>=1,F4-1,F4+11)</td>
</tr>
<tr>
<td>G5</td>
<td>=IF($F5>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F5&$E5)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F5&$E5)))</td>
</tr>
<tr>
<td>H5</td>
<td>=IF($F5>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F5&$E5)*--(TEXT($B$2:$B$8,"myyyy")=$F5&$E5)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F5&$E5)+--(TEXT($B$2:$B$8,"myyyy")=$F5&$E5)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>C6</td>
<td>=IF(B6>0,IF(-1*(NETWORKDAYS(A6,B6))>3,"yes",IF((NETWORKDAYS(A6,B6))>3,"yes","no")),"")</td>
</tr>
<tr>
<td>D6</td>
<td>=IF(C6="yes",NETWORKDAYS(A6,B6)-2,0)</td>
</tr>
<tr>
<td>E6</td>
<td>=IF(AND(E5=YEAR(TODAY()),NOT(F5=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F6</td>
<td>=IF((F5-1)>=1,F5-1,F5+11)</td>
</tr>
<tr>
<td>G6</td>
<td>=IF($F6>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F6&$E6)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F6&$E6)))</td>
</tr>
<tr>
<td>H6</td>
<td>=IF($F6>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F6&$E6)*--(TEXT($B$2:$B$8,"myyyy")=$F6&$E6)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F6&$E6)+--(TEXT($B$2:$B$8,"myyyy")=$F6&$E6)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>C7</td>
<td>=IF(B7>0,IF(-1*(NETWORKDAYS(A7,B7))>3,"yes",IF((NETWORKDAYS(A7,B7))>3,"yes","no")),"")</td>
</tr>
<tr>
<td>D7</td>
<td>=IF(C7="yes",NETWORKDAYS(A7,B7)-2,0)</td>
</tr>
<tr>
<td>E7</td>
<td>=IF(AND(E6=YEAR(TODAY()),NOT(F6=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F7</td>
<td>=IF((F6-1)>=1,F6-1,F6+11)</td>
</tr>
<tr>
<td>G7</td>
<td>=IF($F7>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F7&$E7)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F7&$E7)))</td>
</tr>
<tr>
<td>H7</td>
<td>=IF($F7>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F7&$E7)*--(TEXT($B$2:$B$8,"myyyy")=$F7&$E7)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F7&$E7)+--(TEXT($B$2:$B$8,"myyyy")=$F7&$E7)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>C8</td>
<td>=IF(B8>0,IF(-1*(NETWORKDAYS(A8,B8))>3,"yes",IF((NETWORKDAYS(A8,B8))>3,"yes","no")),"")</td>
</tr>
<tr>
<td>D8</td>
<td>=IF(C8="yes",NETWORKDAYS(A8,B8)-2,0)</td>
</tr>
<tr>
<td>E8</td>
<td>=IF(AND(E7=YEAR(TODAY()),NOT(F7=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F8</td>
<td>=IF((F7-1)>=1,F7-1,F7+11)</td>
</tr>
<tr>
<td>G8</td>
<td>=IF($F8>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F8&$E8)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F8&$E8)))</td>
</tr>
<tr>
<td>H8</td>
<td>=IF($F8>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F8&$E8)*--(TEXT($B$2:$B$8,"myyyy")=$F8&$E8)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F8&$E8)+--(TEXT($B$2:$B$8,"myyyy")=$F8&$E8)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>E9</td>
<td>=IF(AND(E8=YEAR(TODAY()),NOT(F8=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F9</td>
<td>=IF((F8-1)>=1,F8-1,F8+11)</td>
</tr>
<tr>
<td>G9</td>
<td>=IF($F9>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F9&$E9)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F9&$E9)))</td>
</tr>
<tr>
<td>H9</td>
<td>=IF($F9>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F9&$E9)*--(TEXT($B$2:$B$8,"myyyy")=$F9&$E9)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F9&$E9)+--(TEXT($B$2:$B$8,"myyyy")=$F9&$E9)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>E10</td>
<td>=IF(AND(E9=YEAR(TODAY()),NOT(F9=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F10</td>
<td>=IF((F9-1)>=1,F9-1,F9+11)</td>
</tr>
<tr>
<td>G10</td>
<td>=IF($F10>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F10&$E10)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F10&$E10)))</td>
</tr>
<tr>
<td>H10</td>
<td>=IF($F10>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F10&$E10)*--(TEXT($B$2:$B$8,"myyyy")=$F10&$E10)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F10&$E10)+--(TEXT($B$2:$B$8,"myyyy")=$F10&$E10)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>E11</td>
<td>=IF(AND(E10=YEAR(TODAY()),NOT(F10=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F11</td>
<td>=IF((F10-1)>=1,F10-1,F10+11)</td>
</tr>
<tr>
<td>G11</td>
<td>=IF($F11>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F11&$E11)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F11&$E11)))</td>
</tr>
<tr>
<td>H11</td>
<td>=IF($F11>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F11&$E11)*--(TEXT($B$2:$B$8,"myyyy")=$F11&$E11)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F11&$E11)+--(TEXT($B$2:$B$8,"myyyy")=$F11&$E11)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>E12</td>
<td>=IF(AND(E11=YEAR(TODAY()),NOT(F11=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F12</td>
<td>=IF((F11-1)>=1,F11-1,F11+11)</td>
</tr>
<tr>
<td>G12</td>
<td>=IF($F12>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F12&$E12)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F12&$E12)))</td>
</tr>
<tr>
<td>H12</td>
<td>=IF($F12>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F12&$E12)*--(TEXT($B$2:$B$8,"myyyy")=$F12&$E12)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F12&$E12)+--(TEXT($B$2:$B$8,"myyyy")=$F12&$E12)*--($C$2:$C$8="yes")))</td>
</tr>
<tr>
<td>E13</td>
<td>=IF(AND(E12=YEAR(TODAY()),NOT(F12=1)),YEAR(TODAY()),YEAR(TODAY())-1)</td>
</tr>
<tr>
<td>F13</td>
<td>=IF((F12-1)>=1,F12-1,F12+11)</td>
</tr>
<tr>
<td>G13</td>
<td>=IF($F13>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F13&$E13)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F13&$E13)))</td>
</tr>
<tr>
<td>H13</td>
<td>=IF($F13>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F13&$E13)*--(TEXT($B$2:$B$8,"myyyy")=$F13&$E13)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F13&$E13)+--(TEXT($B$2:$B$8,"myyyy")=$F13&$E13)*--($C$2:$C$8="yes")))</td>
</tr>
</table></td>
</tr>
</table>

jfgoodhew1 · Oct 17, 2012

APOLOGIES!!! Excel Jeanie came up with that and I was in source code mode so had no idea what it would look like! Tried using the built in table maker on the forum to replicate the sheet and naturally timed out. That post, I'm afraid, is stuck there until a mod removes it...

In retribution, please see this:
Example formulae and output cells for those who don't like Uploading.com/similar sites

VALUES:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer date
[/TD]
[TD]Actual Date
[/TD]
[TD]Early/Late?
[/TD]
[TD]Days Early/Late
[/TD]
[TD]Year
[/TD]
[TD]Month
[/TD]
[TD]Total Shipments
[/TD]
[TD]Early/Late Shipments Per Month
[/TD]
[TD]Total Days Early/Late Per Month
[/TD]
[/TR]
[TR]
[TD]14/08/2010
[/TD]
[TD]16/08/2010
[/TD]
[TD]no
[/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/11/2011
[/TD]
[TD]27/11/2011
[/TD]
[TD]yes
[/TD]
[TD]3
[/TD]
[TD]2012
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/04/2012
[/TD]
[TD]19/04/2012
[/TD]
[TD]yes
[/TD]
[TD]2
[/TD]
[TD]2012
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/05/2012
[/TD]
[TD]03/05/2012
[/TD]
[TD]no
[/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/05/2012
[/TD]
[TD]04/05/2012
[/TD]
[TD]yes
[/TD]
[TD]2
[/TD]
[TD]2012
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2012
[/TD]
[TD]02/10/2012
[/TD]
[TD]no
[/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/10/2012
[/TD]
[TD]18/10/2012
[/TD]
[TD]yes
[/TD]
[TD]8
[/TD]
[TD]2012
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012
[/TD]
[TD]11
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

FORMULAE:

Column C: =IF(C2="yes",ABS(NETWORKDAYS(A2,B2))-2,0)
Column D: =IF(C2="yes",ABS(NETWORKDAYS(A2,B2))-2,0)
Column E: E2=YEAR(TODAY)
E3==IF(AND(E2=YEAR(TODAY()),NOT(F2=1)),YEAR(TODAY()),YEAR(TODAY())-1)
Column F: F2=MONTH(TODAY())
F3=IF((F2-1)>=1,F2-1,F2+11)
Column G: =IF($F2>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F2&$E2)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F2&$E2)))
Column H: =IF($F2>9,(SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F2&$E2)*--(TEXT($B$2:$B$8,"myyyy")=$F2&$E2)*--($C$2:$C$8="yes"))),SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F2&$E2)+--(TEXT($B$2:$B$8,"myyyy")=$F2&$E2)*--($C$2:$C$8="yes")))
Column H: ???

jfgoodhew1 · Oct 18, 2012

Link taken down, solved query myself:
Total number of days late in a given month =IF($F2>9,SUMPRODUCT(--(TEXT($B$2:$B$8,"mmyyyy")=$F2&$E2)*--($D$2:$D$8)),SUMPRODUCT(--(TEXT($B$2:$B$8,"myyyy")=$F2&$E2)*--($D$2:$D$8)))

How many days late, within date ranges rolling back from today?

jfgoodhew1

New Member

jfgoodhew1

New Member

jfgoodhew1

New Member

jfgoodhew1

New Member

Similar threads

Share this page

How many days late, within date ranges rolling back from today?

jfgoodhew1

New Member

jfgoodhew1

New Member

jfgoodhew1

New Member

jfgoodhew1

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock