IF formulas with AND and OR - comparing dates

[Mariane_Marketi]

Hey guys,

This is driving my brains a bit nuts. I have a base for sales & stock management. In my sales base I have a Date field called INVOICE DATE and a LOOK UP FIELD for the Product's Latest Stocktake.

The formula I am trying to achieve here is:

IF Invoice Date is BEFORE last stock take (including time) then Result is 'BEFORE'

IF Invoice Date is AFTER Last stock take then 'AFTER'

IF Invoice Date is AFTER last stock take AND equals or higher than Today+7 days then 'FUTURE'

If Last Stocktake is Blank then 'NO STOCK'

So far this has worked the closest, although it doesn't have the NO STOCK option and it gets all messed up with I set times and timezones to the date fields.

IF(

{Invoice Date}<{Last stocktake},

"BEFORE",

IF(

AND(

{Invoice Date}>{Last stocktake},

{Invoice Date}>=DATEADD(TODAY(),7,'days')

),

'FUTURE',

IF(

{Invoice Date}>{Last stocktake},

'AFTER'

)

)

)

Can anyone help, please?

[Mariane_Marketi]

So this formula seems to be working for after, future and no stock but not for before:

IF(

{Last stocktake}=BLANK(),

'NO STOCK',

IF(

AND({Invoice Date}<{Last stocktake},

{Last stocktake}

),

'BEFORE',

IF(

AND({Invoice Date}>{Last stocktake},

{Invoice Date}>=Calculation),

'FUTURE',

IF(

AND({Invoice Date}>{Last stocktake},

{Invoice Date}<Calculation),

'AFTER'

)

)

)

)

To see if I was getting the formula wrong, I created a formula field: IF({INVOICE DATE}<LAST STOCKTAKE,'YES','NO') and it comes up with NO. If i change it to Invoice date >Last stocktake then it returns yes which makes no sense, before invoice date is BEFORE last stocktake. Here's a screenshot:

 

[Mariane_Marketi · Accepted solution]

I GOT IT!!! 

Using IS_BEFORE and IS_AFTER solved EVERYTHING. If anyone is interested, here's the forums I used:

IF(

{Last stocktake}=BLANK(),

'NO STOCK',

IF(

AND(IS_BEFORE({Invoice Date},{Last stocktake}),

{Last stocktake}

),

'BEFORE',

IF(

AND(IS_AFTER({Invoice Date},{Last stocktake}),

{Invoice Date}>=Calculation),

'FUTURE',

IF(

AND(IS_AFTER({Invoice Date},{Last stocktake}),

IS_BEFORE({Invoice Date},Calculation)

),

'AFTER'

)

)

)

)