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 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.

Hi

I am struggling to incorporate the max if formula into my existing formula

I am returning the data from a table...This is my formula...I need to include the maxif formula into the false part of the If condition

so basically if R1 = 1 then I don't need to run the maxif formula however if it shows 2, then use the maxif formula to return the last day found and then retrieve the data for that date found

=IFERROR(IF($R$1=1,SUMIFS(Table13[WIP],Table13[Date],'WEEK DASHBOARD'!C$8,Table13[Teams],'WEEK DASHBOARD'!$B9),SUMIFS(Table13[WIP],Table13[Date],[COLOR=#FF0000]???????,[/COLOR]Ta[COLOR=#FF0000][/COLOR]ble13[Teams],'WEEK DASHBOARD'!$B9),Table13[Teams],'WEEK DASHBOARD'!$B9)),0)

 

[Eisasuarez]

Please let me know if it doesn't make sense