Index match return min and max date

[silambarasan.J]

Dear Forum Leader,

Column A&B is contain Date and week Number for financial calendar from "time sheet" and another sheet is required format for "payslip".

To match the year and week no return the starting to end date.

DATA:

DATE******WEEK NO

20/03/2017******51
21/03/2017******51
22/03/2017******51
23/03/2017******51
24/03/2017******51
25/03/2017******51
26/03/2017******51

REQUIRED FORMAT;

YEAR *****2017*****START DATE*****20/03/2017
WEEK NO*****51 *****END DATE *****26/03/2017

[Special-K]

in D3
=INDEX('TIME SHEET'!A$2:A$500,MATCH(B2,IF(B3='TIME SHEET'!B$2:B$500,YEAR('TIME SHEET'!A$2:A$500)),1),1)
Array formula, use Ctrl-Shift-Enter

in D2
=D3-6

[JohnTopley]

=MIN(IF(('TIME SHEET'!$B$2:$B$378=$B$3)*(YEAR('TIME SHEET'!$A$2:$A$378)=$B$2),'TIME SHEET'!$A$2:$A$378))

=MAX(IF(('TIME SHEET'!$B$2:$B$378=$B$3)*(YEAR('TIME SHEET'!$A$2:$A$378)=$B$2),'TIME SHEET'!$A$2:$A$378))

BOTH ..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[kvsrinivasamurthy]

In D2

=INDEX('TIME SHEET'!A2:A378,MATCH(B3,'TIME SHEET'!B2:B378,0))

In D3

=D2+7

[silambarasan.J]

Thank you so much for index formula Special-K and kvsrinivasamurthy.

[Special-K]

kvsrinivasamurthy 's formula is the best as it's not an Array formula.

[silambarasan.J]

JohnTopley: thank you so much for max and min formula.

I have small query suppose in B2 is month and year how do achieve the same result.Its really helpful i have avoid one additional column.

B2 is like Mar-17, April-17, Mar-18.

[JohnTopley]

=MIN(IF(('TIME SHEET'!$B$2:$B$378=$B$3)*('TIME SHEET'!$A$2:$A$378>=$B$2)*('TIME SHEET'!$A$2:$A$378<=EOMONTH($B$2,0)),'TIME SHEET'!$A$2:$A$378))

=MAX(IF(('TIME SHEET'!$B$2:$B$378=$B$3)*('TIME SHEET'!$A$2:$A$378>=$B$2)*('TIME SHEET'!$A$2:$A$378<=EOMONTH($B$2,0)),'TIME SHEET'!$A$2:$A$378))


Not sure why you want month if you have week number.

So what is expected result with month/year?

[avk]

Dera "silambarasan.J", You can also do this. Refer attach file.

In timesheet : Add helper column "C" with formula

Please Login or Register  to view this content.

copy down.
In payslip sheet : in "D2"

Please Login or Register  to view this content.

In "D3"

Please Login or Register  to view this content.

[Array formula, use Ctrl-Shift-Enter]

[silambarasan.J]

thank you so much AVK. its really helpful and learn new thing.

Thanks a lot

[JohnTopley]

MAX (as mentioned in other posts) can be MIN result + 6 so no need for MAX formula.

And why not use the non-array formulae others provided.

[shukla.ankur281190]

In D2

=INDEX('TIME SHEET'!A2:A378,MATCH(B3,'TIME SHEET'!B2:B378,0))

In D3

=D2+7

Nicely done by Kvsrinivasmurthy but there is two years in time sheet column A

When we change in payslip sheet year cell B2 into 2018 it would not work. Then Try

D2

Please Login or Register  to view this content.

D3

Please Login or Register  to view this content.

same as he said.

[avk]

Thanks & welcome "silambarasan.J "
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

[silambarasan.J]

=MIN(IF(('TIME SHEET'!$B$2:$B$378=$B$3)*('TIME SHEET'!$A$2:$A$378>=$B$2)*('TIME SHEET'!$A$2:$A$378<=EOMONTH($B$2,0)),'TIME SHEET'!$A$2:$A$378))

=MAX(IF(('TIME SHEET'!$B$2:$B$378=$B$3)*('TIME SHEET'!$A$2:$A$378>=$B$2)*('TIME SHEET'!$A$2:$A$378<=EOMONTH($B$2,0)),'TIME SHEET'!$A$2:$A$378))


Not sure why you want month if you have week number.

So what is expected result with month/year?

Thank you so much for your reply and response. Its really interesting eomonth and max/min

you are right. If I have a week No there is no need for month. Apology am not explain properly about my requirements.

But my requirement is different. I am try to apply based on your formula, its working but I am not sure it right or wrong?

=MIN(IF(('TIME SHEET'!$D$2:$D$378=$B$3)*YEAR('TIME SHEET'!$B$2:$B$378)=YEAR($B$2),'TIME SHEET'!$B$2:$B$378))
=MAX(IF(('TIME SHEET'!$D$2:$D$378=$B$3)*YEAR('TIME SHEET'!$B$2:$B$378)=YEAR($B$2),'TIME SHEET'!$B$2:$B$378))

Please suggestion. File attached for your reference.

[JohnTopley]

Again ... not clear what you want: week 4 is in April so based on the year, it is returning the correct result.