• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating Monthly Payment Date

I want to build a formula that I can drag down to replicate the string of dates in the attached file. Based on a start date and anniversary date, I would like to have the date increase each row by one month, with the same day value as the anniversary date, unless that day falls on a weekend or holiday (in which case, move forward to the next non-holiday workday).

Any ideas?
 

Attachments

  • test.xlsx
    14.5 KB · Views: 12
OK Jeffrey - here's my crack at it ....

H7:I26 are my calculations - I named the range f7:f24 "Holidays"

I based the test around the Workday() function as follows

=WORKDAY(H7-1,1,Holidays)=H7 - TRUE is a Workday - FALSE is either a weekend or a Holiday from the named range

The red dates in col H are ones which fall on a Weekend or Holiday, although I don't think the latter has been tested ... Anyway, give it a run and see if you can break it ...

Use the file in the following message - it's the latest incarnation
 
Last edited:
OK - well I tested a little more and it fails if the Friday and the following Monday are Holidays ... will need one more IF in that case, but there will also be other ways of generating the correct result that the Ninjas:awesome: will be along shortly with ...

Fixed the above issue with most recent file
 

Attachments

  • Test -DME effort.xlsx
    17.3 KB · Views: 12
Last edited:
Back
Top