Help

IF formulas with AND and OR - comparing dates

Topic Labels: Formulas
Solved
Jump to Solution
682 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Mariane_Marketi
6 - Interface Innovator
6 - Interface Innovator

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?

 

1 Solution

Accepted Solutions
Mariane_Marketi
6 - Interface Innovator
6 - Interface Innovator

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'

)
)
)
)

See Solution in Thread

2 Replies 2
Mariane_Marketi
6 - Interface Innovator
6 - Interface Innovator

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:
 
Screenshot 2023-05-13 at 12.42.38 pm.png

Mariane_Marketi
6 - Interface Innovator
6 - Interface Innovator

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'

)
)
)
)