Is the date between those 2 dates

[david20100]

I have some product, and the date they have been created

Product	Prod-End
CA-200	2015-04-07
CA-200	2015-04-07
CA-200	2015-04-07
CA-200	2015-04-07
CA-200	2015-04-07
CA-200	2015-05-22
CA-200	2015-05-22
CA-200	2015-05-22
CA-200	2015-05-22
CA-200	2015-05-22
CA-200	2015-06-15

I also have a DB that returns me a value (Time)  if the production end date (Prod-End) is between the Start and End dates

Product	Time	Start	End
CA-200	5	1999-01-01	2015-10-04
CA-200	11	2015-10-05	2016-11-02
CA-200	15	2016-11-03	2017-12-05
CA-200	12	2017-12-06	2018-06-23
CA-200	10	2018-06-24	2099-09-16

I dont know if it's because my brain is stuck in flu i can't figure how to make it works

[cpituley]

Hi @david20100, 

Looks like you want to start out with an inner join on the two data sets with [Product] = [Product]. 

Then use the multifield formula to say (for a new named field)

IF [Prod-End] > [Start] and [Prod-End] < [End] THEN 0 ELSE 1 ENDIF

This should flag all product line entries that were created outside of the date range with a "1". 

Hope this helps. 

EDIT: You can also use the filter function after the join and filter the date based on the above stated criteria.

Thanks,

Cameron

[david20100]

Maybe i'm not clear in my explanation

For this example

Product	Prod-End
CA-200	2015-04-07

It should return me 5 for that entry because its between those 2 dates

Product	Time	Start	End
CA-200	5	1999-01-01	2015-10-04

[cpituley · Accepted solution]

Hi @david20100, 

My apologies, I misunderstood. 

The following change should accomplish what you are looking for: 

IF [Prod-End] > [Start] and [Prod-End] < [End] THEN [Time] ELSE 0 ENDIF

This will return 0 when it's not between those 2 dates and the value in the [Time] field when it is. Feel free to replace 0 with whatever you want to show up when it isn't between the date range.

Let me know. 

Thanks,

Cam

[david20100]

Thanks that's a good idea, after that formula i applied a filter to remove all 0 to keep only the good data

thanks again