Dates between if statement challenge.

[CEB]

I have been stuck on this challenge for quite a while.

I am trying to adjust volume this period to zero in a new column, if none of the dates within 6 different estimate ranges, fall within a 45 day time period, starting with the current date.

Excel data file link.

Any help would be a life saver! I have tried multiple variations of if statements and dates between functions with no luck. 

[Anonymous]

Hi,

Have you tried something like this with IF / AND / DateDiff statements?

 Volume This Period New = IF(
AND(DATEDIFF(MAX(data[Estimate 1: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 1: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 2: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 2: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 3: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 3: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 4: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 4: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 5: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 6: En]), NOW(), DAY) >= 45 ,

DATEDIFF(MAX(data[Estimate 6: St]), NOW(), DAY) >= 45)))))))))), 0, SUM(data[Volume This Period]))

[CEB]

martynbooth88, 

Thanks for looking into this. I really appreciate it. I tried the formula that you posted, but made one change to not sum the volume in the end, so that it copies over the original volume per each row. I have never used the DateDiff function before. Unfortunalty I still can't get it to do what I need. I marked up an image if you have any further thoughts on it. Since today is 7/17/2018 and based on the dates in the estimate ranges, the yellow ones fall within the 45 day window starting from today. Those that don't have any dates that fall within the 45 day window should then be set to zero. 

Again, I don't want to take too much of your time. If you can think of a way to make that happen, I would be very grateful. 

[CEB]

If I use the DateDiff function on each estimate start and stop date from today's date, I get a positive or negative number. 

Estimate 2: En# = DATEDIFF(NOW(), data[Estimate 2: En], DAY)

Anything greater than zero and less than 45 is what I am looking for. Now I think I need to figure out how to look at all 12 date options and if just one of them is greater than zero and less than 45, use the original volume value, otherwise set to zero. 

[Vvelarde · Accepted solution]

@CEB

Try withs calculated column:

Column =
IF (
    DATEDIFF ( TODAY (), Table1[Estimate 1: St], DAY ) > 0
        && DATEDIFF ( TODAY (), Table1[Estimate 1: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 1: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 1: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 2: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 2: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 2: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 2: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 3: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 3: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 3: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 3: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 4: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 4: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 4: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 4: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 5: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 5: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 5: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 5: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 6: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 6: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 6: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 6: En], DAY ) <= 45,
    Table1[Volume This Period],
    0
)

Regards

Victor

[CEB]

That worked great! Thank you both @Anonymous and @Vvelarde.