CODEWITHSUNDEEP
excelexcel-formula
Maykid
Maykid

Reputation: 517

How do I only get future dates only instead of future and past dates?

So this is the issue that I'm facing. Currently I'm running into an issue with a piece of my code. What I'm looking for is to only grab things that haven't reached a specific end date and if it's already ended do not grab, but currently it's grabbing everything.

I've tried doing some IF statements for example:

{=IF('Sheet1'!$G:$G>TODAY(),IFERROR(INDEX('Sheet1'!$D:$D,SMALL(IF('Sheet1'!$E:$E=$B$1,ROW('Sheet1'!$E:$E)-MIN(ROW('Sheet1'!$E:$E))+1),ROWS('Sheet1'!$D$2:D2))),""),"")}

NOTE: This is an Array

But running this still gives me Category's that have already past the End Date.

For Column Reference:

Sheet1 Column D = Category

Sheet1 Column E = Name

Sheet1 Column G = End Date

B1 = Name of person (Currently a drop down list of multiple names)

The Current code I have without the IF statement is as follows: (Again Array)

=IFERROR(INDEX('Sheet1'!$D:$D,SMALL(IF('Sheet1'!$E:$E=$B$1,ROW('Sheet1'!$E:$E)-MIN(ROW('Sheet1'!$E:$E))+1),ROWS('Sheet1'!$D$2:D2))),"")

What I want it to result in is if the End Date is in the past to essentially skip over and move onto the next criteria instead of grabbing it based of TODAY() function. With the result of only capturing future Category's with future End Dates.

Any help would be much appreciated!

-Maykid

Upvotes: 1

Views: 378

Answers (2)

Krysteel
Krysteel

Reputation: 61

You can use an if statement similar to 

if(edate(range)>Today(),return this data,0)

Insert your data for return this data.

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26650

As an array formula:

=IFERROR(INDEX('Sheet1'!$D:$D,SMALL(IF(('Sheet1'!$E:$E='Sheet1'!$B$1)*('Sheet1'!$G:$G>TODAY())>0,ROW('Sheet1'!$D:$D)),ROW(A1))),"")

Upvotes: 2

Related Questions