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
- 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
- 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.
- 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)
- 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?
--
decrements the value by 1, if that's what you're expecting. (Instead, I think it negates and then negates again, yielding no change.)--
is used to convert Boolean results to number, it's valid and widely used.