I need a function or combo of functions that will give me the date of a given day in a month.
Like if I want the first Saturday or the fourth Monday, how do I ask Excel to give me that date?
Oh, I don't do macros, just never learned how. So I greatly prefer to find the date with some sort of easily coded function.
Any help is greatly appreciated. Thanks
I was able to come up with three methods, one of which is a simple user-defined function. Hopefully you will be able to see how each works. I can't test directly if this will work in Excel 2003 for you, but I did save it in the 97-2003 format from 2007.
If you can't figure out how a formula works, try stepping through it using "Evaluate Formula" in the Formula Auditing menu item in the tools menu!
If you want to see where your code went wrong, try stepping through it by clicking in the code and pressing F8 and watch as the magic happens!
If you are happy with any of the results, please add to the contributor's reputation by clicking the star icon.
If you want to try a UDF
1/. Press alt+f11 to open the VBA Editor
Please Login or Register to view this content.
2/. In the editor ..... Insert>Module
3/. Paste all the code in the new module
4/. Close and return to Excel
5/. In any cell
The order is Year (eg 2010), Month (1 to 12), Day (1 to 7 , Sun = 1), Reqd rank (1 to 5)Please Login or Register to view this content.
there is no 4/5th occurence #ERROR# will be displayed.
Omitting the last option will return the first day required
Omitting the last 2 No. options will return the first Monday of the month
Returns the first Monday in JanuaryPlease Login or Register to view this content.
Format the cell to suit your date style as normal
deke, what would your input look like? If you want a macro-free solution to find the third Tuesday of a month, how would want you specify that in your sheet? Can you upload a data sample of your sheet, with the structure and data types that you are currently using?
Everstriven - thanks. Just what I needed.
FWIW, a formula based route which requires no helpers etc might be as follows
(using everstrivin's sample file)
=DATE($C$2,$D$2,1)+MOD($A$2-WEEKDAY(DATE($C$2,$D$2,1),2),7)+7*($B$2-1)
Where:
A2 holds the weekday number based on a Mon-Sun week (ie Mon is 1, Sun is 7)
C2 holds Year
D2 holds Month
B2 holds Instance
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
DonkeyOte
It's weight in Gold might I suggest?FWIW, a formula based route which requires no helpers etc......
I have a lot to learn before charging down the UDF route!
Cheers
Alistair
Most if not all of the date formulae I post are based largely (if not entirely) upon posts made previously by daddylonglegs ... those I do cobble together myself he can normally streamline further (as is I'm sure the case here - if he sees it)
Say hello to Fife for me... the years spent at St.Andrews were good ones
Some good pubs there, a'what?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks