Extracting Mondays from a list of dates

[macaonghus]

I have a sequential list of dates (typically Mon-Fri, but not always, due to public holidays) and I am trying to extract the first day of each week into a new column.

I've spent hours trying vlookup, offset, match, indirect...but I can't get it to come together.

How can I do this? Thank you!

Weeks sample.xlsx

[AliGW]

Have a look at this thread from earlier today: http://www.excelforum.com/excel-form...mn-a-to-e.html

[azumi]

Something like this?

[macaonghus]

Something like this?

Azumi, yes, close...your example picks up Mondays, but the working weeks don't always start on Mondays. 27/12/1995 and 02/01/1996 should make it onto the output column, and they don't in your example.

[martindwilson]

since dates are numbers you can use this
=IFERROR(1/LARGE(INDEX(($A$2:$A$38="start of week")*(1/$C$2:$C$38),0),ROWS($A$1:A1)),"")

[macaonghus]

since dates are numbers you can use this
=IFERROR(1/LARGE(INDEX(($A$2:$A$38="start of week")*(1/$C$2:$C$38),0),ROWS($A$1:A1)),"")

Genius! Solved.

[macaonghus]

Thank you all - ExcelForum is such a satisfying website, the amount of Excel knowhow on here is astounding.

[MarvinP]

Hi macaonghus,

I need to learn more about array formulas and your problem was a chance for me to learn. I added some helper columns to help explain what I've done. See another answer for your problem in the attached. It uses the Array formula (Need to Ctrl+Shift+Enter when entering the formula).