Last day of month with no other similar named day.

[Eljefegeneo]

Because of our unique billing schedule, I need to know the last day of a month that does not have another similar named day. For example, Wednesday, April 24 would be the last day without an additional Wednesday in the month. (April 23, a Tuesday would not be because there would be a Tuesday, April 30th). Similarly, Friday, May 25th would be the last day because there would be no more Fridays in the month. (It wouldn't be Thursday the 24th, because there is one more Thursday in the Month).
Is there any way to do this? Thanks.

 

[Mr. B]

Take a look here:
http://forums.codeguru.com/showthread.php?335395-VBS-Find-last-Thursday-of-Current-Month-VBS

I tried it and this will return what you are looking for.

 

[Eljefegeneo]

I tried it but it didn't do anything. But in any case I don't know what the "last day" of the month as I outlined above is; could be any day of the week. I don't want the last weekday of the month, I need the last day of the month that doesn't have another same named day in that month.

Am I not understanding the code you referenced?

 

[94Sport5sp]

Hi:

NextMonthDate = dateadd("m",1,Now)

This will give you the date one month from today.

LastDate = DateSerial(Year(NextMonthDate), Month(NextMonthDate),1)

This will give you the first day of next month. Subtract 7 from LastDate and you will be and the last non repeating weekday for this month.

 

[Eljefegeneo]

Super. Now I just have to figure out how to substitute the Month And Year I want to use instead of Now(). For example, if I chose May 2013 instead of Now(), I assume it will give me the last day of the month that I want. I'll be back if i can't figure it out, but I am sure that I can. Easy formulas like yours are great and easy to change. It's the complex ones that give us novices headaches.
Good job!!

 

[Mr. B]

I tested The code from the link that I directed you to and I would suggest that you take another look at it because it does not return just the "last day of the month". It returns exactly what you said you wanted. It will return the last "Thursday" or which ever specified "Day" you specify.

Just copy the code and paste it into a Public Function in a module. Put a bread point in the code and run it to test it.

 

[94Sport5sp]

Hi Eljefegeneo:

Super. Now I just have to figure out how to substitute the Month And Year I want to use instead of Now(). ... ... Easy formulas like yours are great and easy to change. It's the complex ones that give us novices headaches.
Good job!!

Glad I could help. Let us know how you make out or if you need more help.

 

[Solo712]

Because of our unique billing schedule, I need to know the last day of a month that does not have another similar named day. For example, Wednesday, April 24 would be the last day without an additional Wednesday in the month. (April 23, a Tuesday would not be because there would be a Tuesday, April 30th). Similarly, Friday, May 25th would be the last day because there would be no more Fridays in the month. (It wouldn't be Thursday the 24th, because there is one more Thursday in the Month).
Is there any way to do this? Thanks.

Take a look at this thread. It may be just what you are looking for. For example, if you want the last Wednesday in the current month you simply call the function as follows, NthWeekday("LastWednesday", Month(Date), Year(Date)).

Best,
Jiri

 

[Eljefegeneo]

Form for the query/report now works like a charm! Using the Next month date, last date, etc., changed to my needs I now only have to select the month of the report, it automatically updates the from and two fields, and voila, the query and report is done!
Thanks again for your help 94Sport5sp!