Date Formula Query

[Bready340]

HiEveryone,



I'm going round in circles and cannot figure out a formula for thebelow, I really need some help.

The criteria needs to determine whether a specific delivery date is greaterthan 70 days from a given date(this could be a date in the future or the past)and return a specific value (“Backlog”if the delivery date is greater than 70days and “Not Backlog” if the delivery date is under the 70 days). However, ifthe customer has agreed to take the delivery after 70 days (specified in adifferent column) then I need the formula to return “Not Backlog”, or “Backlog”if this date becomes more than 70days from “today”.

Ive tried using nested IF AND functions but I can never seem to get theright result!

Any help or advice would be greatly appreciated!

Thanks!

 

[JazzSP8]

Looking at the first row which seems to be a problem example.

The days between 07/08 and 02/10 is 56 - Which is less than 70
The days between 07/08 and 23/11 is 108 - Which is more than 70
The days between 02/10 and 23/11 is 52 - Which is less than 70
The days between 02/10 and 24/11 is 53 - Which is still less than 70

We've no way of saying that on 24/11 its going to be a Backlog.

Well, none that I can see any way

 

[JazzSP8]

So in the first example, the order was created on the 7/8, but it is Not backlog as the customer agreed to take it on the 23/11. This will become backlog on the 24/11.
In the last example, the order was promised to be delivered on the 10/10, but is still in the open order book and therefore a Backlog.

Just a thought, is this a tracking workbook? You said "This will become backlog" and "still in the open order book" which makes me think that it is?

If so then could you check the agreed despatch date with the current date to see if it's a back log?

=IF(C2>TODAY(),"Not Backlog","Backlog")

If it's not then ignore this post, just a random thought I had to try and get this working for you.

 

[Bready340]

Thanks Jazz,

Yes, it will be a tracking workbook, I think a lot of the issue I the criteria and definition of Backlog!

You're right with using "Today", but need to incorporate the AND function to allow for the rest of the criteria!

Thanks for all your help!