Find the third Wednesday in a month?

  • Thread starter Thread starter Sven
  • Start date Start date
S

Sven

Hi,
I’m trying to write a function which always returns the third Wednesda
in a month? Is this possible by using a combination of different dat
functions?

Example:
Return the third Wednesday between the two dates 2004-06-01 til
2004-06-30

Additional question:
Do I have to specify two dates? Or can I just specify for exampl
May04, Jun04 July04?

Thanks
/Sve
 
Hi
see:
http://www.cpearson.com/excel/DateTimeWS.htm

In your case use
=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

This translates if A1 contains a date (e.g. the start of your month)for
your example to:
=DATE(YEAR(A1),MONTH(A1),1+((3-(4>=WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)))
)*7)+
(4-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))))

And as you see you don't need the interval
 
=DATE(YEAR(A1),MONTH(A1),1+21)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),4))


when I type Jun04 in my excel I automatically get June 04 2004 which will
work but if you have text you would need
to parse out the month and match it against a text string then parse out the
years as well.
So I recommend that you type the first date of the month in the cell


=DATUM(ÅR(A1);MÅNAD(A1);1+21)-VECKODAG(DATUM(ÅR(A1);MÅNAD(A1);4))

in Swedish
 
Sven,

No, you don't need to specify 2 dates.

If your date in A1 is any date of the month:
=A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+4) ' will give the 3rd Wed

If your date in A1 is the first of the month, you can abbreviate as follow:
=A1+21-WEEKDAY(A1+3)

Regards,

Daniel M.
 
Sven said:
Hi,
I?m trying to write a function which always returns the third Wednesday
in a month? Is this possible by using a combination of different date
functions?

Example:
Return the third Wednesday between the two dates 2004-06-01 till
2004-06-30

Additional question:
Do I have to specify two dates? Or can I just specify for example
May04, Jun04 July04?

Thanks
/Sven
Click to expand...

You could give this a try also:

=EOMONTH(A5,0)-WEEKDAY(EOMONTH(A5,0)+4)+1
 
Sven said:
Hi,
I?m trying to write a function which always returns the third Wednesday
in a month? Is this possible by using a combination of different date
functions?

Example:
Return the third Wednesday between the two dates 2004-06-01 till
2004-06-30

Additional question:
Do I have to specify two dates? Or can I just specify for example
May04, Jun04 July04?

Thanks
/Sven
Click to expand...

sorry; my last post returns the last wed and not the 3rd wed
 
Hi Sven,
With any date in the month you want in B1
=DATE(YEAR(B1),MONTH(B1),14+CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1),1)),4,3,2,1,7,6,5))

WEEKDAY Worksheet Function (1=Sun, 2=Mon, 3=Tues, 4=Wed, 5=Thur, 6=Fri, 7=Saturday)
so rotate the choose dates so that the day you want is first.
The first possible day of month that the third Wed can occur on is the 15th.

more examples in
First, Last, Nearest, Closest, nth weekday (4th Monday) in Month, Date calculations (#firstdate)
http://www.mvps.org/dmcritchie/excel/datetime.htm#firstdate
 
David McRitchie said:
With any date in the month you want in B1
=DATE(YEAR(B1),MONTH(B1),14+CHOOSE(WEEKDAY(DATE(YEAR(B1),
MONTH(B1),1)),4,3,2,1,7,6,5))
Click to expand...
....

Seems a bit verbose, at 8 function calls down to 4 nesting levels, when it
could be done with 3 function calls down to just 1 nesting level
(What's the advantage of this
approach?
 
Hi Harlan (and Daniel),
Thanks for pointing that out, I should have looked at the whole thread,
but without your heads up I certainly would have missed how much
shorter and simpler the solution by Daniel M. was than what I've been using..
 
Back
Top