Hi - Dave here.
Happy Friday!
Last week, I shared a formula to list semimonthly pay dates that land on the 1st and 15th of each month.
Sharp readers pointed out that when these dates land on a
weekend, they are typically adjusted to the
previous business day.
How can we get Excel to make this adjustment for us?
The trick is to run the dates through the WORKDAY function with a special configuration like this: =WORKDAY(date+1,-1)
You can see the result of this approach in the worksheet below. The original dates appear in column D. Column F shows the adjusted dates. Notice that the four dates that land on weekends have been shifted to the
previous Friday:
[
Download the workbook and read the full explanation]
If you need a custom work schedule (like a 4-day week Mon-Fri), you can switch to the WORKDAY.INTL function, which makes it easy to designate any day of the week as a workday. Click the link above to read the updated explanation and to download the updated workbook. The worksheet above is on Sheet5.
Last day for VBA School
The special offer for Victor Chan's "Launch Excel Macros and VBA School" course is ending today at 12 midnight Pacific Time. If you've been thinking about improving your Excel skills with VBA, now is the time.
The course includes over 10 hours of video lessons, practical projects, some great bonuses, and personalized support from Victor himself. Plus, with the $300 discount, you'll save 60% on the full list price of $497. Use the link below to learn more and enroll:
Click to join VBA School now
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
