There is a price list by date range.
9/1/2020..9/30/2020 $400
10/1/2020..blank $500
I would like to have report for price base on the date. I have tried
=NL("Rows","Purchase Price","Purchase Price","Item No.","@@"&$G8,"Starting Date","<"&$E8,"Ending Date",">"&$E8)
But it did not work exactly what i want. If the ending date is blank, it did not work.Please help.
Hi Masashi,
have you tried using "ending date","@@>"&$E8? not sure if it will work with ">" in the formula, but in general the jet does not accept blank reference hence why you are getting an error
https://support.jetglobal.com/hc/en-us/articles/218953758-Referencing-a-Blank-cell-with-
Hi Yuko Saito,
Thanks. I have tried with "@@". Actually, there is 2 conditions to check:
if the reference date is between September 1 to September 30 - the price is $400.
If the reference date is later than September 30, the price is $500.
at the NAV, i have to filter like the screenshot below.
At the Jet report:
I have to check the reference date vs ending date,
If ending date > reference date , give me $400
if ending date is blank , give me $500
If you need when the ending date is greater than E8 or blank, the correct format should be ">"&E8&"|@@". You need to have the @@ at the end otherwise Jet will interpret everything after the @@ as the value to look for.
If you use "@@>"&$E8, Jet is looking for the date with the literal value of >10/12/20 which is not a date.
I would also recommend using .. instead of greater than and less than when filtering on dates. I get less errors and it is easier to interpret, in my experience. That is just my own opinion though