Hello I am using the most recent version of Excel to maintain a data sheet of multiple accounts with various due dates. I have a column that list the payment due dates for each jurisdiction, another column where I type the last date it was actually paid. I WANT another column that will automatically calculate, in date format mm/dd/yyyy, when the next payment is due. The problem is that my dates vary for each jurisdiction and I can't figure out a formula that will work on all. I have some that are due annually, other are due quarterly, a few are due February and June of each year, some are due on the 1st some are due on the 5th, etc.I am currently using the formula below but it only works if due dates coincide with the vlookup or allow me to have a set patter, which they don't. Is there 1 formula I can use for the entire spreadsheet that will calculate my next payment due date?

COLUMN A COLUMN B COLUMN C

Actual Payment Due Date Most Recent Date Taxes Paid NEXT PAYMENT DUE DATE 2/28, 6/15, 11/5 11/5/2018 ??/??/????=EDATE(Q3,CEILING(DATEDIF(Q3,A1,"m")+0,VLOOKUP(M3,{"monthly",1;"quarterly",3;"semi-annual",6;"annual",12},2,0)))

Thank you, in advance

Please click on the following link and repost your example data after reading the How-To. Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History