Log In
Register
Home
January 12, 2023
Offline
Hello all experts, I have a task which needs to return every first and third Wednesday of each month and every second and fourth Monday of each month. So four dates of each month will be return on some months and 3 dates will return on some months.
The tricky part is, we don\'t use the ISO week system. We simply use board cast calendar. Meaning that for example July 2023, 1/7/2023 is Saturday, and this is the first week. So no first Wednesday will be return on that month and the third Wednesday should return 12 July 2023 and fourth Monday will be 17 Jul 2023 and so on using the same algorithm for every month and every year. So if I type 2023 on a cell. The following dates should display to me: 4/1/23, 18/1/23, 9/1/23, 23/1/23 , 1/2/23, 15/2/23, 6/2/23 20/2/23, 1/3/23, 15/3/23, 6/3/23,20/3/23, 12/4/23, 3/4/23,17/4/23, 3/5/23, 17/5/23, 8/5/23, 22/5/23, 14/6/23, 5/6/23, 19/6/23, 12/7/23, 3/7/23, 17/7/23, 2/8/23, 16/8/23, 7/8/23, 21/8/23, 13/9/23, 4/9/23,18/9/23, 4/10/23, 18/10/23, 9/10/23, 23/10/23, 1/11/23, 15/11/23, 6/11/23, 20/11/23, 13/12/23, 4/12/23, 18/12/23.
So some of the months only return 3 dates because their 1st day of that month is not a Wednesday. Is there any way by using formula to achieve this, please advise.
Thank you so much in advance.
See Answer
Trusted Members
October 17, 2018
Offline
I found this code a long time ago and it works great
[code]
' We can generalize this to holidays that are defined as the Nth Day of some month,
' such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.
' The following function will return the Nth DayOfWeek for a given month and year:
'SYntax NDOW:
' y = Year
' M = Month
' N = Nth day of M month
' DOW = Day of the week: 1 = Sunday, 2= Monday, etc.
Public Function NDow(y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
NDow = DateSerial(y, M, (8 - WeekDay(DateSerial(y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
End Function
[/code]
VIP
Trusted Members
December 7, 2016
Offline
Hello Tango,
This blog article gives a good description of how to get the 1st or 3rd (and so forth) weekday of a month, check it out.
https://www.myonlinetraininghu.....each-month
I can't find any calendar system named board cast, so I assume you are referring to the broadcast calendar system, so the trick here would be to adjust the ISO week system to reflect this.
The first Wednesday in the broadcast month of July in the year 2023 is 28th of June, but as per your description you don't want to list that date for July, even though it is in July, as per the broadcast calendar rules. How come?
Please upload a sample file, so we don't have to spend time to create a data sample, which in the end might not be what you want.
Br,
Anders
January 12, 2023
Offline
Hi Anders,
Thank you so much on your reply. The link you sent I actually look at it before and nevertheless, somehow this is the ISO calendar.
Maybe I shouldn't use the word first and third Wednesday and second and fourth Monday. This confuse people.
Indeed, what I want is the the first week and third week Wednesday of every month plus the second week and fourth week of Monday as well.
The way I want to see it as first week is by the 1st of that month, no matter which day it is. So for example, June 2023; the first day of June is on Thursday. So 1/6, 2/6 and 3/6 is the first week. Thus, that's why in my previous message, I say I would expected a return of 14/6/23, 5/6/23, 19/6/23. Because 14/6 is the third week Wed and 5/6 is the second week Monday and 19/6 is the fourth week Monday.
Sorry for the confusion.
Moderators
January 31, 2022
Offline
Perhaps the attached workbook is helpful. With a few helper columns (just to make it easier to follow) it will put a TRUE or FALSE on every date in the year, based on the principle you described. I checked the TRUEs against your list and it agrees.
Trusted Members
October 17, 2018
Offline
Applying my NDOW macro and some own inventivity .... this is the maco version.
You can always translate the macro into a formula if you do not want to use VBA
VIP
Trusted Members
December 7, 2016
Offline
Hello,
The result you want is not per the Broadcasting Calendar system, but never mind, Hans has provided you with a VBA solution and here is another formula solution, see attached file. I have set up highlighting of dates as per the Broadcasting Calendar and the Gregorian Calendar systems, you will also find a full year date list as per the Gregorian Calendar system.
Br,
Anders
January 12, 2023
Offline
Thank you so much guys. Indeed, some problem was found on both Hans and Anders's solution.
I found that in Feb 2023, the second week Monday which I was hoping to get 6/2 and it return 13/2 and the fourth week of Feb, I was hoping to get 20/2, it return 27/2. Same thing happen on March ,August and November 2023 too.
However, the rest of the months shows correctly.
Riny worksheet is right, it just that instead of showing true or false, I was hoping to get a dates of the whole year just like what Anders and Hans does.
Moderators
January 31, 2022
Offline
Hi Tango,
My schedule merely listed all dates with TRUE and FALSE as I had not understood in what other format you wanted the dates. Filter out the FALSE's to show only the TRUE's. Or create a simple pivot table to display only the TRUE dates. Added the latter to my original file. Attaching it again.
Riny
January 12, 2023
Offline
Hi Riny,
Thank you so much on your reply. Your work is nice.
I look back on Anders and Hans solutions, all those problem months first day was on either Wednesday or Tuesday. If there is a way in either formula or vba to solve it, that will be most perfect.
Yet your work is nice, if I could find a way by enter a year in a cell, and all 365 days will be auto generated on the col A, yet those true or false value will then be updated based on the latest value of col A. It will still do the job even though it looks a bit bulky.
January 12, 2023
Offline
I finally manage to modify Riny's work. Now dates will be auto generated after entering a year.
I also used Index + aggregate together to return all True value.
Looks bulky but it shows what I needed.
I also attached my work here for someone who may need it in future.
Once again. My sincerely appreciation to Riny, Anders and Hans. You guys are amazing.
Answers Post
1 Guest(s)
