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

 

[Eisasuarez]

I might be able to have a helper column but if it csn be done in 1 cell that would be better - thanks

 

[Joe4]

OK. I will give your the "helper" column way, in case no one comes up with a way to do it without.

Let's say your data is in range A2:A100.
Then in cell B2, enter this formula, and copy down to row 100:

=YEAR(A2) & WEEKNUM(A2)

And if your date is in cell D1, enter this formula in D2:

=YEAR(D1) & WEEKNUM(D1)

Then, you can use this formula to get what you want:

{=MAX(IF($B$2:$B$100=D2,$A$2:$A$100,0))}

Note that the squiggly brackets indicate that the formula must be entered with CTRL-SHIFT-ENTER.

 

[Geoff Taylor]

{=MAX(IF($A$7:$A$100>=A7,IF($A$7:$A$100<=(A7+8-WEEKDAY(A7)),$A$7:$A$100,0)))}

Assuming your list starts in A7. When entering the formula, use ctrl+shift+enter.

Edit - I didn't see Joe's post above. I think my formula will work in a single cell.

 

[Joe4]

{=MAX(IF($A$7:$A$100>=A7,IF($A$7:$A$100<=(A7+8-WEEKDAY(A7)),$A$7:$A$100,0)))}

Assuming your list starts in A7. When entering the formula, use ctrl+shift+enter.

Edit - I didn't see Joe's post above. I think my formula will work in a single cell.

Where does the matching with the date in cell D1 come in?

 

[Geoff Taylor]

My mistake ...

{=MAX(IF($A$7:$A$100>=D1,IF($A$7:$A$100<=(D1+8-WEEKDAY(D1)),$A$7:$A$100,0)))}

 

[Joe4]

My mistake ...

{=MAX(IF($A$7:$A$100>=D1,IF($A$7:$A$100<=(D1+8-WEEKDAY(D1)),$A$7:$A$100,0)))}

I would love to see a single formula solution. So I was doing some testing, and it looks promising. However I ran into an issue.

Let's say that among all our dates in column A, we have 1/9/2016 (which is a Saturday) and 1/10/2016 (which is a Sunday).
Then, if we have 1/4/2016 in cell D1, it should return 1/9/2016, but it is returning 1/10/2016.

 

[63falcondude]

I would love to see a single formula solution. So I was doing some testing, and it looks promising. However I ran into an issue.

Let's say that among all our dates in column A, we have 1/9/2016 (which is a Saturday) and 1/10/2016 (which is a Sunday).
Then, if we have 1/4/2016 in cell D1, it should return 1/9/2016, but it is returning 1/10/2016.

I think the OP wants the end of the week to be Sunday.

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

 

[Joe4]

I think the OP wants the end of the week to be Sunday.

I missed that reply in the flurry of replies...
In that case, it looks like Geoff's code might do the trick!!!

 

[Eisasuarez]

Thank you all - i will give that a go and test it

the reason why i needed to retrieve the last day found in that week is because on a daily basis, we run a script to get all the incoming for that day...now we may not run the script everyday therefore i needed to get the ladt possible date and data found in that week...

thank you

 

[mohan.r1980]

Hi Friend,
Considering Saturday is the week end
try below formula
may be it will help you

=DATE(YEAR(A2),MONTH(A2),DAY(A2+SUM(WEEKDAY(7)-WEEKDAY(A2))))