Excel Formula to List First Monday in each Month

[Excelambda]

Hi, simple and elegant solution like always. Tried to find an approach for other occurrences, like 2nd, 3rd, 4th, and sometimes the 5th occurrence. And I came with this.
The layout is simple, A1 to A7 weekdays strings Mon …to Sun, B1 has the year, C1 the day of the week (data val list A1:A7), and in D1 occurrence nr (data val list 1, 2, 3, 4, 5).
The formula is =DATE(B1, SEQUENCE(12), 1)+MOD(7-WEEKDAY(DATE(B1, SEQUENCE(12), 1), 2)+XMATCH(C1, A1:A7), 7)+7*(D1-1).
(Use of MOD solves the offsetting problems, so no need for extra IF conditions for particular weekdays).
Everything nice and clean for occurrences 1st, 2nd, 3rd and 4th. To solve the 5th, that happens only for some months and not all of them, we need a very simple IF that makes the formula grow, but the logic is simple, on the month that have only 4 occurrences we have to extract 7 days, and leaves a elegant result. So, for the last occurrence in every month we choose 5 in cell D1.
Final : =IF(MONTH(DATE(B1, SEQUENCE(12), 1)+MOD(7-WEEKDAY(DATE(B1, SEQUENCE(12), 1), 2)+XMATCH(C1, A1:A7), 7)+7*(D1-1))<>SEQUENCE(12), DATE(B1, SEQUENCE(12), 1)+MOD(7-WEEKDAY(DATE(B1, SEQUENCE(12), 1), 2)+XMATCH(C1, A1:A7), 7)+7*(D1-1)-7, DATE(B1, SEQUENCE(12), 1)+MOD(7-WEEKDAY(DATE(B1, SEQUENCE(12), 1), 2)+XMATCH(C1, A1:A7), 7)+7*(D1-1)) .
ONE cell array formula for any occurrence 1st, 2nd, 3rd, 4th and using 5 for last occurrence.
Note: Used WEEKDAY( , 2) but makes no difference.
Sorry for the long post !! Not convinced that is simple enough but could not came with a better one, so far. Peace!!

[Fabi_terra]

Hi Mynda. I cannot express how much this video has helped me! This brought me very close to the solution I needed. Once again, it was an incredible tutorial. Thank you so much!🤗

[johnabram]

Hi Mynda, following is my solution
{=IF(WEEKDAY(DATE(2020, ROW(1:12), 7), 12)<>7, DATE(2020, ROW(1:12), 7)-WEEKDAY(DATE(2020, ROW(1:12), 7), 12), DATE(2020, ROW(1:12), 7))}
Though it gives the same result but its not as elegant as yours.

[afhlmd]

amazing technique Mynda! Thanks for sharing!

[cSkylaneRG]

I've got a unique one for ya: How would I configure a cell such that, if I enter a date in a source cell, it will either give me the date a week later (+7) or a specific date further down the road. For example: if I enter January 24, 2022, I'd want it to return April 1, 2022, but if I enter July 24, 2022, I'd want excel to return July 31, 2022. Thanks! PS: I'd want to be able to reuse the formula for subsequent years without having to edit it. Thanks again!

EDIT: Figured it out: I use YEAR(), MONTH(), and DAY(), to extract those aspects of a known date, and then use a crap-ton of IF()s to lay out what I'm after. Just needed to think about it for awhile (and know that YEAR, MONTH, and DAY even existed as formula terms). Thanks anyway! :)

[joelgoom]

Thought it worth mentioning that for the weekday for the end of the prior month is the same weekday as the 7th day of the current month. So the formula could also be written =date(yyyy, row(1:12), 7)-weekday(date(yyyy, row(1:12), 7), 3)

[darrylmorgan]

Hi Mynda!Really Helpful Tutorial Thank You :):)

[citizengoodman]

This was helpful and I can probably figure how to do what I need to do, which is pull every Friday in each month.

[tangoc]

Very great video. Indeed, if I want to add some public holiday, so that for example if one of the first day of Monday hit the public holiday, it will automatically add 7 days to display. How will this possible to work?

[mrsalexandra]

Clear and easy to follow! Can you tell me how I would calculate the first Monday after the first Friday of each month please? Thanks

[zahidsayed]

HOW CAN WE LIST THE DATES OF A PARTICULAR DAY OF A WEEK ? YOUR TIPS WERE AWESOME, KEEPUP THE GOOD WORK.

[margoczaplewski]

I want to pull every monday for the year. How would I modify the formula to accommodate that?

[smartshanu]

Great thanks for sharing this formula Mynda. Could you please help me how to use this formula to find date of 2nd and 3rd monday of every month in a year.

[MrRicks]

How can I create a monthly timsheet with fixed weekly format monday to sunday. 1st of every month should auto fits into fixed weekly format?

[williamtim]

Hi thank you, how do list every Saturday date?

[gregdennis]

Does the concept work if you need the Monday date for every Monday in a year? Used in Shipping.

[maheshalex]

Thank you, can you people help to find last Friday of each month 🙏

[PAKBOOKKEEPINGSERVICES]

Plz let me know if I use "Sumif Formula* in report sheet (sheet 1 ), when I filterer my data in data sheet(Sheet 2 ) sumif formula must pick (Sum) only faltered data. is it possibly??? Plz reply .

[derekpage]

Hi Mynda, when I click on the link for the download it doesn't seem to connect?

[shauste]

Thanks for the tutorial, but I can't seem to get it to work properly. When I press Enter, I get the first date then 5 digits numbers for the next 11 rows. Example" 1/4/2021 followed by 44228, 44256, 44291... Can you tell me what I could be doing incorrectly?