Hi - Dave here.
Happy Friday!
In my last few emails, I've shared some formulas that generate lists of dates, generally using a combination of the SEQUENCE function and the WORKDAY.INTL function. This week, I want to share a more advanced formula to list semimonthly pay dates. A semimonthly pay schedule means employees are paid twice a month, usually on the 1st and 15th or the 15th and the last day of the month. This is a bit tricky to do with an Excel formula, but it can be done!
You can see what this looks like in the worksheet below, where we use the SEQUENCE function to generate dates, and the FILTER function to find pay dates that fall on the 1st and 15th for the first half of a year:
[
Download the workbook and read the full explanation]
Note we are also using the LET function to avoid redundancy and make the formula easier to read. Click the link above for a full explanation. The formula is easy to modify to target other dates of the month. I've also included some formulas that work in older versions of Excel.
VBA School
Victor's VBA school course is still open until September 22. Victor gets a lot of good feedback on the course and I wanted to share two examples:
George Lee, Aviation Industry: "
I joined Launch Excel VBA School a few months ago. It's a well-designed learning experience. The course itself is well laid out and progressively takes a student from the basics up to useful fluency. Of course, you need to put the work in to make VBA work, but with the help of this course, it's much faster to get somewhere useful. In addition, I found that Victor replies very quickly to messages with requests for help and was very responsive to a request I had on how to fix my VBA code to activate a worksheet by year. It really helps to get quick feedback."
Philip Shekoni, MBA, CFA, Senior financial analyst, Canada: "
From someone who has taken different courses just to learn VBA (and most of the others costing multiples of what this one costs), this is one of the best courses on VBA that you'd find around. The building bricks are laid exceptionally well and in an order that makes for real understanding. The videos and projects are also really easy to follow. The instructor communicates well and is very responsive to questions. The course is very good for beginners and also has a lot to teach already-users. Finally, in case you didn't figure it out already, this costs much lower than what I would think the average cost for good VBA courses would be, and represents great value for money."
If you want to learn VBA from the ground up, this course is a great option. You can find more detailed information here:
Click here for all the details
The special price of $197 ends Friday, September 22.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
