0

I have 2 columns of dates, Expected delivery and Delivery.

They could look like this:

expected    delivery
12-08-2015  10-08-2015
12-08-2015  12-08-2015
12-08-2015  14-08-2015
01-09-2015  06-09-2015
02-09-2015  06-09-2015
03-09-2015  06-09-2015
04-09-2015  06-09-2015
05-09-2015  06-09-2015
06-09-2015  06-09-2015
07-09-2015  06-09-2015
08-09-2015  06-09-2015
05-09-2015  
06-09-2015  
07-09-2015  
08-09-2015  
09-09-2015  
10-09-2015  

I want to perform a series of different counting operations on these data.

1 and 2 Should only be performed if a date is in Expected and Delivery

  1. Deliveries "On time" are all rows were Expected <= Delivery
    • =SUMPRODUKT(--(H:H>I:I)) this formular how ever produces a number of false positives for rows with no deliver date
  2. Deliveries "Late" are all rows where Expected > Delivery
    • =SUMPRODUKT(--(H:H

3 and 4 Should only be performed if a date is in Expected and Delivery is empty.

  1. Deliveries "Due" are all rows where Expected is <= todays date + 2
    • =SUMPRODUKT(--(H:H>A7)) seams to work for all Due, but seams to me that it could produce false positives (A7 is todays date + 2)
  2. Deliveries "Over due" are all rows are > todays date
    • =SUMPRODUKT(--(H:H>A6)) works for over due dates bur produces a ton of false positives for deliveries already made.

Wishes for a solution:

a. Count how many "on time", "late", "due" and "overdue deliveries my spreadsheet has. Eliminating false positives.

b. Highlight all "Late" rows with yellow, "Due" rows with orange, and "Over due" rows with red. Must be possible with Formatting, I've been chasing the right numbers for a start.

Bonus Wish:

c. an additional column contains the responsible for the delivery, is it possible to collect all "due" and "over due" cases in an e-mail and send to the person responsible for the case?

6
  • 1
    Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including the code they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for code are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?.
    – DavidPostill
    Sep 6, 2015 at 11:53
  • I'm not an Excel expert, but I doubt -- decrements the value by 1, if that's what you're expecting. (Instead, I think it negates and then negates again, yielding no change.)
    – Arjan
    Sep 6, 2015 at 12:27
  • I actually have the calculations set about now, I'll post them a little later!
    – Jgaard
    Sep 6, 2015 at 15:26
  • You show the logic reversed. Late should be Delivered > Expected. On-time should be Delivered <= Expected. In general, though, you're requesting an application. Each of your problems should be a separate question.
    – fixer1234
    Sep 6, 2015 at 18:59
  • 2
    @Arjan: -- is used to convert Boolean results to number, it's valid and widely used. Sep 7, 2015 at 4:48

1 Answer 1

1

Answer for part 1. of your question:

Your formulas were close, just you need to add another condition:

  • on time =SUMPRODUCT(--($A$2:$A$18>=$B$2:$B$18),--ISNUMBER($B$2:$B$18))
  • late =SUMPRODUCT(--($A$2:$A$18<$B$2:$B$18),--ISNUMBER($B$2:$B$18))
  • due =SUMPRODUCT(--($A$2:$A$18<=TODAY()+2),--($B$2:$B$18=""))
  • overdue =SUMPRODUCT(--($A$2:$A$18>TODAY()+2),--($B$2:$B$18=""))

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .