LinkBack URL
About LinkBacks
Hello there,
I'm trying to calculate if a delivery is late or not. I was wondering if you'd please be able to help with this.
This is based on the date of the order, +1 (item is dispatched the next working day after being ordered).
The delivery period is 5 working days and this is counted from the next working day after being ordered.
The order date is entered into cell F21.
Cell F27 checks if F21 is not empty - if true, it calculates the difference between F21 and today's date, excluding weekends and the specified holidays in range K12:K21.
=IF(F21<>"", IF(NETWORKDAYS(F21+1, TODAY(), K12:K21)>5, "LATE", "NOT LATE"), "thinking")
This test case identifies the problem:
F21's date is entered as 12/04/15 (a Sunday).
Today's date is 19/04/15 (a Sunday).
F27 resolves to NOT LATE.
I know that this is because my formula is counting the working days between 13/04/15-17/04/15, which equals 5. As the result is exactly 5 (and not greater than 5), the formula resolves to false (NOT LATE).
But what if the delivery never arrived on the 17th and I query this on the 18th or 19th? The formula lies to me and tells me it's not late!
I also know why this is, because the formula is only counting working days and won't resolve to true (LATE) until today's date is 20/04/15 or greater.
So, I *think* I need to do something like this:
Somehow get the date that NETWORKDAYS says is the 5th day (in this case, 17/04/15) from:
=NETWORKDAYS(F21+1, TODAY(), K12:K21)=5
Then check if today() is greater than that date (regardless of non-working days). In this scenario, as today is greater than 17/04/15, I would get my desired result (LATE).
Assuming that I'm thinking on along the right lines here, is there any function that will do the same as NETWORKDAYS but return the 5th day as a date, so I can accomplish my master plan?!
*befuzzled* I think it's time for coffee and biscuits...
Oh yes, this is in Office 2013, Windows.
thank you for any hints or help you can offer
Register To Reply
Bookmarks