Variable to give last working day of previous month

[a_mahey]

Hello,

Can someone please help? I need to setup a variable which can be called PreviousMonthWorkDay but i want this variable to give me the last working day (Monday to Friday) of the previous month based on today's date.

Many thanks in advance.

 

[Special-K99]

Could you base something on this?

=WORKDAY(DATE(YEAR(A1),MONTH(A1),1),-1)
returns last working day of previous month

Source: http://www.mrexcel.com/forum/excel-questions/1828-last-working-day-month.html

 

[RickXL]

Hi,

You could also use:
=WORKDAY(EOMONTH(TODAY(),-1)-7,5)
That finds the last day of the previous month then subtracts a calendar week (7 days) then adds a working week (5 days).

In VBA that looks like:

PreviousMonthWorkDay = Application.WorkDay(Application.EoMonth(Now, -1) - 7, 5)

The "Now" function could be changed for a date constant or a date variable, if needed.

 

[a_mahey]

Thank you all for your help much appreciated. I was looking for a VBA solution. RickXL your solution worked perfectly.

On the other side, I want to expand my knowledge of VBA, I learnt it originally by using Bill Jelen's video tutorials but now want to expand it further by learning about Functions, Arguments and Arrays. Can you guys please suggest some more videos/DVDs that i could purchase which will teach these? I find videos a lot more helpful than reading books. Also, I want to learn about other keywords that you used above like "EoMonth", is there a source that i can go to which will teach me all these different keywords which I don't even know exist.

Many thanks in a

 

[RickXL]

Hi.

Thanks for the feedback.

You could start with the Excel Tutorials here: Microsoft Office support - Help, training & tutorials

EoMonth is actually a worksheet function, the sort of thing you use in a worksheet formula. You can use them in VBA as well by putting "Application." in front of them.

The Excel Date functions should be required reading for anyone who uses dates or times in Excel: https://support.office.com/en-in/ar...eference-8df854dc-a7b4-4af6-b34c-9f3175da0451

To get a feel for the way to do things in Excel I usually go to either Chip Pearson's site: CPearson.com Topic Index
or to the OzGrid site: Excel Templates | Excel Add-ins and Excel Help with formulas and VBA Macros

There is always the MrExcel site, of course. Amongst other things, they have all kinds of video tutorials: MrExcel.com | Excel Resources | Excel Seminars | Excel Products

Regards,