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!

 

[JimmyG64]

What date are you referring to by "this date"? (underlined below)

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!

 

[Bready340]

Hi Jimmy,

That would be the delivery date, sorry I didn’t make it very clear!

Thanks

 

[Bready340]

Hi Jimmy!

That would be e delivery date sorry I didn't make it very clear!

Thanks!

 

[JazzSP8]

You've not given a data structure, so...

Col A = Delivery Date
Col B = Given Date
Col C = Agreed (Y/N)

Headers in Row 1 (as above), Data from Row 2

Formula in D2;

=IF(B2-A2<=70,"Not Backlog",IF(AND(B2-A2>70,C2="Y"),"Not Backlog","Backlog"))

Not sure if that gets where you need to be but it'll at least point you in the right direction.

Post back if you need more help.

 

[Bready340]

Thanks JazzSP8, i'll have a go with that!

Sorry for the lack of information, it my first time posting!

 

[JazzSP8]

Sorry for the lack of information, it my first time posting!

No problem - You just need to remember that we can't see your data and we've no idea how your Workbook is set out so the more information you can give helps us, help you

 

[Bready340]

Thanks Jazz!

Sorry, I still cant get the formula right! Heres a snap shot of the data;[TABLE="width: 339"]
<colgroup><col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <tbody>[TR]
[TD="width: 88, bgcolor: #FFFF99"]Created on[/TD]
[TD="width: 81, bgcolor: #FFFF99"]Req. Desp.[/TD]
[TD="width: 109, bgcolor: #FFFF99"]Agreed Desp. Date[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]07/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]02/10/2018[/TD]
[TD="bgcolor: transparent, align: right"]23/11/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Not backlog[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]29/10/2018[/TD]
[TD="bgcolor: transparent, align: right"]07/01/2019[/TD]
[TD="bgcolor: transparent, align: right"]07/01/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13/04/2018[/TD]
[TD="bgcolor: transparent, align: right"]27/11/2018[/TD]
[TD="bgcolor: transparent, align: right"]04/12/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14/05/2018[/TD]
[TD="bgcolor: transparent, align: right"]10/10/2018[/TD]
[TD="bgcolor: transparent, align: right"]10/10/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Backlog[/TD]
[/TR]
</tbody>[/TABLE]


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.

Does this make sense?

Thanks for any input

 

[JazzSP8]

From what you've said, the first example wouldn't be a back log because it's less than 70 days between 07/08 and 02/10?

The last example would be a Backlog because there is more than 70 days between 14/05 and 10/10 regardless of what is in the agreed column...?

 

[Bready340]

Hi Jazz,

For the second line yes, you are correct. With the first line the customer agreed take delivery later than the requested date, therefore making it not backlog. If that makes sense?

Sorry, this is not my criteria, its what ive been tasked with! Thanks for you help!