Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

adding by month and depending on day

0

I am trying to get excel to add 1 month to previous date and if the new day falls on a weekend (sat or sun) to desplay the next day. however the dates in the spreedsheet are the actual dates needed for a few months but it will be over 5 years. The due date is 15th of each month or the next working day.

I added a second file with some formula in it....

please see attached

Answer
Discuss

Answers

0
Selected Answer

Albert

Firstly, I'd normally use EDATE to add (or subtract) one or more months (say from the 15th of a month) e.g. if the base date is in cell B5:

=EDATE(B5,1)
where the 1 is the number of months to be added etc.. 

In this case, to avoid non-working days, I suggest a (sneaky) combination of EOMONTH - end of month- and the WORKDAY function.

With your base date of the 15th, EOMONTH can get the date of the end of the previous month and adding 7 to that gets the 7th,

The WORKDAY function can get a working day which is a number of working days from that. To get to the 15th (from the 7th and assuming a 2 day weekend of Saturday and Sunday)  I add 6  which equates to 8 calendar days less a 2 day weekend (so 6 more working days from the 7th).

In the attached file you will see a formula using an incrementing helper column (A) which is (for E6):

=WORKDAY(EOMONTH(B$5,-1+A6)+7,6
 where the 6 in bold is that 6 working days to get to the 15th or next working day (and column C converts that date shows what day that is). That gives the 15th of each month or nearest working day with no weekend days.

If you don't want to use a helper column (e.g. repayment numbers), then ROW can be used (with a constant subtracted from it) so the alternative in H6 (e.g.) is:

=WORKDAY(EOMONTH(B$5,ROW(B6)-6)+7,6)
Note that WORKDAY also has a third argument for holidays to skip (so you can point it to cells where local holdays are listed).

If you don't use a Saturday and Sunday weekend, the function WORKDAY.INTL (workday international) allows you to specify non-working patterns (via another parameter).

Hope this works for you. 

Discuss

Discussion

Oops! Just corrected my Answer (which was missing a comma in the last formula )
John_Ru (rep: 4332) Oct 29, '21 at 4:58 pm
Hi John.
Thanks for answerring. the formula sort of works but the dates get out of link (?) pretty quickly. I guess they need to be the 15th or the next workday if on weekend

I uploaded another file with the formula
wildecoyote1966 (rep: 28) Oct 29, '21 at 9:02 pm
Hi Albert. I've revised/corrected my Answer (sorry but I made the first one over breakfast and ahead of what became an 8-hour drive)
John_Ru (rep: 4332) Oct 30, '21 at 4:44 am
Thanks John.
I only changed the column reference and now it does what I need. Now I just nee to workout the interest rate lol
wildecoyote1966 (rep: 28) Oct 30, '21 at 9:47 pm
NP Albert. On calculating the interest rate, if your mortgage provider has confirmed how it is calculated then it shouldn't be too hard to implement in Excel. Should you get stuck, feel free to ask a new question on that.
John_Ru (rep: 4332) Nov 1, '21 at 4:50 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login