how to return first date value in a list that equals the current month

[BigPapaMurf]

Having trouble with 2 formulas...

Each column has 3 cells (plus header) There are 2 to 3 values that can be returned (In this case, biweekly paychecks where a couple months a year there are 3 pay periods)

The value = a specific date (pay date) in mm/dd/yyyy format

The value is found on another sheet based on the current year using (INDIRECT(YEAR(TODAY())

This can possibly be done 2 ways?

Option 1 (not sure if it's possible to create the second)
The first cell in the column should lookup the first value in a list that equals the current month.
The second cell in the column should lookup the second value in a list that equals the current month.
The third cell in the column should lookup the third / last value in a list that equals the current month.

Option 2 (more likely)
The first cell in the column should lookup the first value in a list that equals the current month.
Second cell = First cell + 14 days
Third Cell = If second cell + 14 days = next month, then return "", otherwise third cell = Second cell + 14 days

To get the first date value from the list, The formula that I was thinking would work is...

MATCH(TRUE,INDIRECT(YEAR(TODAY())&"!$B$7:$B$32")>MONTH(TODAY()),0)
or
MATCH(TRUE,INDIRECT(YEAR(TODAY())&"!$B$7:$B$32")=MONTH(TODAY()),0)

but nogo. Should i use an Index or lookup function instead?

To get cell the second and third cells to work. I thought this formula would work, but it does not.

IF(A3+14<>MONTH(TODAY()),"",A3+14)

Sample workbook attached.

[AliGW]

MATCH just returns the row number, so you need this:

=INDEX(INDIRECT(YEAR(TODAY())&"!$B$7:$B$32"),MATCH(TRUE,INDIRECT(YEAR(TODAY())&"!$B$7:$B$32")>MONTH(TODAY()),0))

To get it to increment, add this:

=INDEX(INDIRECT(YEAR(TODAY())&"!$B$7:$B$32"),MATCH(TRUE,INDIRECT(YEAR(TODAY())&"!$B$7:$B$32")>MONTH(TODAY()),0)+ROW(A1)-1)

Copy down.

[Kaper]

Here:
INDIRECT(YEAR(TODAY())&"!$B$7:$B$32")>MONTH(TODAY())
days (dates) are compared with months. in all cases any date will be higher than any month number.
Try for instance:

Please Login or Register  to view this content.

or INDEX but using INDIRECT(YEAR(TODAY())&"!$B$7:$B$32")>EOMONTH(TODAY(),-1)+1 in MATCH

and for A5:

Please Login or Register  to view this content.

EDIT:
>EOMONTH(TODAY(),-1)
or
>=EOMONTH(TODAY(),-1)+1
shall be used. Otherwise you will not notice first day of given month

[Kaper]

Yet another option with VLOOKUP in one column (note not exact match - 4th parameter equal TRUE or omitted, and +14 to look for latest date not later than 14th day of month)

Please Login or Register  to view this content.

And corrected version with INDEX:

Please Login or Register  to view this content.

Note usage of EOMONTH(TODAY(),-1) (not+1 as we compare as "greater than", not "greater or equal to")

In 3 tables in attachment all 3 solutions are presented

[BigPapaMurf]

Thank you both!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Kaper]

Thanks for marking thread SOLVED and for reputation point.

Which solution have you finally adopted?