LinkBack URL
About LinkBacks
I need a macro which, when given the date (eg StartMonth = 01/04/2006) of the first day of any month, will return the date of the third Friday of same month (eg ThirdFriday = 21/04/2006)
Thanks
Astrikor
I need a macro which, when given the date (eg StartMonth = 01/04/2006) of the first day of any month, will return the date of the third Friday of same month (eg ThirdFriday = 21/04/2006)
Thanks
Astrikor
Take a look at Chip Pearson's date formulas
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Hi,
You can use the below function to arrive at the 3rd Friday of the month
Enter the 1st day of month in A1 and this formula will give the date when it would be 3rd friday.
Please Login or Register to view this content.
Shijesh Kumar
http://shijesh.wordpress.com/
Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.
Moved to Functions Forum
Hi
If you need the result in a cell you could use something likewhere A1 contains the original date and B1 the third Friday.Please Login or Register to view this content.
In codeRegardsPlease Login or Register to view this content.
Jeff
Actually I do need a macro for this application, not a worksheet function (please see my original post).Originally Posted by royUK
The macro will involve an "if" condition which on detection of a date will replace it with the date of the third Friday for the same month and year. This will be applied to a table in which dates will be randomly located. I am looking for the appropriate code for the conditional statement.
Jeff's suggestion might be adaptable.
Any other ideas?
Many thanks
Astrikor
Last edited by astrikor; 01-13-2009 at 04:54 PM. Reason: clarity
Hello Shijesh,
This formula doesn't work in all circumstances. When the 3rd Friday of the month is on the 21st it gives the 14th, e.g. August 2009. This formula will do it
=A1-WEEKDAY(A1+1)+21
...somebody else will have to suggest some code, though.......
Unfortunately, this formula does not always give the correct result either.
If for example A1 is 06/06/2009 it reurrns 26/06/2009 (the 4th Friday).
However, trawling through the forum I came across a formula which does appear to work. It is a more general formula for finding the date of any WEEKDAY for any recurrence. Recording the formula as a macro realises the following code:
I might be able to use this, but would prefer a less cumbersome version using variables which would make it independent of cell location.Please Login or Register to view this content.
Still looking for suggestions!
Thanks
Astrikor
Have you looked at Chip Pearson's date examples?
astrikor,
This should work for you. Just change the source and destination range.
Hope this helps.Please Login or Register to view this content.
Works fine in Excel 2007 in a button and on Worksheet_Change event.
You could use this function:
E.g., =NthWeekDay(2008, 4, 6, 3) returns 18 Apr 2008.Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
I didn't explicitly state as such but I was making the same assumption as Shijesh, that A1 would contain the 1st of the month.
If A1 can contain any date then the formula can be adjusted like this to give the 3rd Friday of that month
=A1-DAY(A1)-WEEKDAY(A1-DAY(A1)+2)+22
Last edited by daddylonglegs; 01-15-2009 at 05:19 PM.
Yup, and by making source and destination cells the same it simply overwrites any date in the cell with the third Friday for that date.
Excellent - just what I need !
Many thanks Extrapulp
Astrikor
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks