FredRock53
New Member
- Joined
- Dec 10, 2020
- Messages
- 3
- Office Version
- 2010
- Platform
- Windows
- MacOS
I am having difficulty describing what I need a formula to display.
It's probably why I can'y get my head around how to do it.
Take 1/1/2021 as a starting point it is the first Friday of the month. 8 is the second Friday and 15 is the third. In the same month and year the first Monday is the 4th and the second Monday is the 11th and so on. So on any given date I need to know weather it is the First, Second, Third, Fourth or Fifth occurrence of its particular day of the week.
I am not talking about this... =WEEKNUM(A2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1))+1
It just gives the week number the day falls in regardless of the day that starts the week.
I was poking around with =WEEKDAY((EOMONTH(A2,-1))) to return the day of the week the month starts on.
But I can't get it to do what i need....
It's probably why I can'y get my head around how to do it.
Take 1/1/2021 as a starting point it is the first Friday of the month. 8 is the second Friday and 15 is the third. In the same month and year the first Monday is the 4th and the second Monday is the 11th and so on. So on any given date I need to know weather it is the First, Second, Third, Fourth or Fifth occurrence of its particular day of the week.
I am not talking about this... =WEEKNUM(A2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1))+1
It just gives the week number the day falls in regardless of the day that starts the week.
I was poking around with =WEEKDAY((EOMONTH(A2,-1))) to return the day of the week the month starts on.
But I can't get it to do what i need....
date | day | day occurrence |
1/1/2021 | Friday | 1 |
1/2/2021 | Saturday | 1 |
1/3/2021 | Sunday | 1 |
1/4/2021 | Monday | 1 |
1/5/2021 | Tuesday | 1 |
1/6/2021 | Wednesday | 1 |
1/7/2021 | Thursday | 1 |
1/8/2021 | Friday | 2 |
1/9/2021 | Saturday | 2 |
1/10/2021 | Sunday | 2 |
1/11/2021 | Monday | 2 |
1/12/2021 | Tuesday | 2 |
1/13/2021 | Wednesday | 2 |
1/14/2021 | Thursday | 2 |
1/15/2021 | Friday | 3 |
1/16/2021 | Saturday | 3 |
1/17/2021 | Sunday | 3 |
1/18/2021 | Monday | 3 |
1/19/2021 | Tuesday | 3 |
1/20/2021 | Wednesday | 3 |
1/21/2021 | Thursday | 3 |
1/22/2021 | Friday | 4 |