Excel - find last day of that week

[Eisasuarez]

Hi

I have dates in column A and Values in Column B

Now say i input a date in D1 = 04/01/2016

In E1, how can i return the last date found for that week looking into column A?

so say the last date for that week was 08/01/2016 - i would want it to return that date..

thank you

 

[63falcondude]

What result you expect in E1 if D1 = 09/01/2016 (i.e. January 9th 2016)? Do you want the last day to be the last workday which would be Friday?

 

[Geoff Taylor]

Try this
=A1+(7-WEEKDAY(A1))

 

[Joe4]

Try this
=A1+(7-WEEKDAY(A1))

I had to read the question a few times myself, I don't think this is what they are after.
It sounds to me like they have a list of dates in column A, and for any given week, they want to know the date of the latest day for that week that appears in column A.
So, it could be a Thursday, a Friday, etc. depending on the dates in column A.

One follow-up question to the original question though. For your purposes, what is the last day in your week? Is it Saturday, or are you looking more for a work week, meaning Friday?

 

[Geoff Taylor]

Reading it again and you may well be right. That's not so easy!!

 

[63falcondude]

This is what I took from the question.

Excel 2010
	A	B	C	D	E
1	Date			Date	Result
2	9/12/2016			9/13/2016	9/15/2016
3	9/13/2016
4	9/14/2016
5	9/15/2016
6	9/17/2016
7	9/18/2016
8	9/19/2016
9	9/20/2016
10	9/21/2016
11	9/22/2016
12	9/23/2016
13	9/24/2016
14	9/25/2016
15	9/26/2016
16	9/27/2016
Sheet1

You would expect the answer to be Friday 9/16/2016 but since that isn't in column A, it finds the next closest date in that week that is in column A.

 

[Eisasuarez]

Hi Joe - yes you are right- i need the last day found in the week in column A - the week could go upto Sunday

 

[63falcondude]

Not sure if my logic is correct here but see if this works

Excel 2010
	A	B	C	D	E
1	Date			Date	Result
2	9/12/2016			9/12/2016	9/18/2016
3	9/13/2016
4	9/14/2016
5	9/15/2016
6	9/17/2016
7	9/18/2016
8	9/19/2016
9	9/20/2016
10	9/21/2016
11	9/22/2016
12	9/23/2016
13	9/24/2016
14	9/25/2016
15	9/26/2016
16	9/27/2016
Sheet1

Cell Formulas
Range	Formula
E2	=INDEX(A2:A16,MATCH(D2+(8-WEEKDAY(D2)),A2:A16,1))

Edit: Realized this won't work if the input date is a Sunday, it returns the following Sunday date.

 

[63falcondude]

Please delete

 

[Joe4]

Edit: Realized this won't work if the input date is a Sunday, it returns the following Sunday date.

Also doesn't seem to work in other situations.

For example, in column A, let's say that we have the dates 1/4/2016, 1/5/2016, and 1/8/2016 among other dates in other weeks.
In column D, if our date is 1/4/2016, it should return 1/8/2016. But the formula you posted returns 1/5/2016.

I think I have a solution that works, but it involves using "helper" columns, if the OP is open to that idea...