Calculating Payment Due Dates Automatically

Hello all, I'm new here and Excel. I require some assistance with the creation of a formula.

Purpose: To automatically calculate the payment next due dates based on a commencement date and today for a subscription based services with varying periods. It needs to update the 'payment next due' as time goes on without user input.

Problem: How do I get the 'Payment Next Due' column to automatically do that aforementioned calculation?

Thank you all!

Non-editable pictures are of NO USE. Please upload a sample EXCEL file here, that meets our guidleines (see yellow banner - top of screen).

Hi. One row with a formula that does not work isn't very helpful, either. Your explanation is far from complete.

1. Re-post the file WITHOUT links to your real data. the dropdowns are not working and the named ranges are referring to your real sheet.

2. Add 5-10 rows with expected results (as requested in the yellow banner that I asked you to look at).

Hello, I am no longer interested in getting help at this time. I apologize that I have wasted your time, I will try my best to figure it out on my own. Thank you for your time Glenn.

You clearlymisu derstand how the forum works. We do this for fun, for free. We therefore expect you to make it immediately clear to us what you want. In short, you need to put some effort into making your problem clear to us.

In your file, there was no indication of your expected answers and your dropdown did not work.

You are of course welcome back at any time, but are also free to go elsewhere if you are unwilling to make the effort and provide a clear set of instructions.

Hello, I have struggled to try and get anything working for this. I am again asking for your help.

I have:
- Reworked the mock-up Excel Spreadsheet.
- Added between 10-20 rows.
- Removed all Excel personal data.
- Stripped the file of all personal meta data.
- Created some more remarks in the file.
- Removed any of the spreadsheet external links and lists.

Attached to the first post is my mock-up spreadsheet. I know I don't deserve help but any would be appreciated.

Please let me know if you need more context or content to work with. Thank you!

Still not clear...

I haven't done w & d yet, and don't get the same answer as you for Q.

B9. Quarterly due 15/5/22. Then 15/8/22, 15/11/22 then 15/2/23.... why is your expected answer 15/4?? A bit confused. Please refer to the file for formula (so far) and explain.

Please review in this post : https://www.excelforum.com/excel-for...ml#post5665550
it's may help.

Regards.

Originally Posted by Glenn Kennedy

Still not clear...

I haven't done w & d yet, and don't get the same answer as you for Q.

B9. Quarterly due 15/5/22. Then 15/8/22, 15/11/22 then 15/2/23.... why is your expected answer 15/4?? A bit confused. Please refer to the file for formula (so far) and explain.

Wow, that is amazing. That is exactly as intended minus the w & d (as you stipulated). Your fast response was very impressive. With regards to the quarterly, I miscalculated all of them. The answers you provided was the intended dates. It looks like you have the general idea of what I was trying to do. Sorry for the mistakes, is there anything else unclear?

Just saw this, let me have a look. Thank you for the link!

Next step. I forgot to include the Billing Quantity. Now included. I still haven't quite got my head round the weeks part, yet.


There was a training space " d" in the red cells that got in the way. deleted.


Check this over.

Ignore days... it's wrong. I will have it (and weeks) fixed in a few minutes.

Originally Posted by Glenn Kennedy

Next step. I forgot to include the Billing Quantity. Now included. I still haven't quite got my head round the weeks part, yet.


There was a training space " d" in the red cells that got in the way. deleted.


Check this over.

That was so fast, I can't imagine how many years of Excel experience you have. I can't even comprehend some of the formula at face value. I will have to break it down. I didn't notice the space. I will have to work on minimizing such errors.
Thank you for your time Glenn, it is greatly appreciated.

I >>>THINK<<< it's complete now!! My brain hurts. there's a possibility that it might be one day out for w and (maybe) d. I don't think so, but it's over to you to check.

Originally Posted by Glenn Kennedy

I >>>THINK<<< it's complete now!! My brain hurts. there's a possibility that it might be one day out for w and (maybe) d. I don't think so, but it's over to you to check.

Your solution to this issue was decidedly remarkable, Glenn! I was at a total loss as to how to address it, but you managed to do so with utmost efficacy. My appreciation for your effort and expertise is boundless. Thank you!
+ Rep

Glad we got there... despite a bit of a temper tantrum (from both parties!!) at the start. All rattles now safely stowed back in our prams (do you have the expression "throwing your rattle out of the pram" for a bit of a tantrum... you probably do).

I'm also glad I don't have to unpick it again and fix further faults. It was a bit complicated.

I could not suppress a chuckle at the sight of your expression. Indeed, you were accurate in your assessment. Having said that, the blunder was exclusively mine; I had been somewhat tense the day before and should not have submitted a hastily completed post and reacted irately when I was the one seeking assistance. Wishing you the best of luck Glenn.

Hahaha. Time to hit the beach, now! Checking out... G.

It occurred to me, while walking, that q and y are not needed, as these are the same as 3 and 12 m. So with free entry of a number in quantity and a dropdown of d, w and m for units, it could be made a fair bit simpler.

Interested?

That's quite smart Glenn, it'll make it more clean. Would I use data evaluation to create a drop-down list?
Is this better? I just removed the q and y from the list.

PHP Code: 

=IF(F5="w",C5+7*INT(DATEDIF(C5,TODAY(),"d")/7)+7,IF(F5="d",C5+IFERROR(1/(1/(E5*(INT((TODAY()-C5)/E5)))),E5),EDATE(C5,E5*LOOKUP(F5,{"m"},{1})*(INT(DATEDIF(C5,TODAY(),"m")/(E5*LOOKUP(F5,{"m"},{1}))+1))))) 


Originally Posted by josephteh

Try this formula,

PHP Code: 

=CHOOSE(LOOKUP(F4,{"d","m","q","w","y"},{1,2,3,4,5}),
INDEX(DATE(YEAR($C4),MONTH($C4),DAY($C4)+ROW(INDIRECT("1:10000"))*$E4),MATCH(TRUE,DATE(YEAR($C4),MONTH($C4),DAY($C4)+ROW(INDIRECT("1:10000"))*$E4)>=TODAY(),0)),
INDEX(DATE(YEAR($C4),MONTH($C4)+ROW(INDIRECT("1:10000"))*$E4,DAY($C4)),MATCH(TRUE,DATE(YEAR($C4),MONTH($C4)+ROW(INDIRECT("1:10000"))*$E4,DAY($C4))>=TODAY(),0)),
INDEX(DATE(YEAR($C4),MONTH($C4)+ROW(INDIRECT("1:10000"))*$E4*3,DAY($C4)),MATCH(TRUE,DATE(YEAR($C4),MONTH($C4)+ROW(INDIRECT("1:10000"))*$E4*3,DAY($C4))>=TODAY(),0)),
INDEX(DATE(YEAR($C4),MONTH($C4),DAY($C4)+ROW(INDIRECT("1:10000"))*$E4*7),MATCH(TRUE,DATE(YEAR($C4),MONTH($C4),DAY($C4)+ROW(INDIRECT("1:10000"))*$E4*7)>=TODAY(),0)),
INDEX(DATE(YEAR($C4)+ROW(INDIRECT("1:10000"))*$E4,MONTH($C4),DAY($C4)),MATCH(TRUE,DATE(YEAR($C4)+ROW(INDIRECT("1:10000"))*$E4,MONTH($C4),DAY($C4))>=TODAY(),0))) 


Hey, thank you for your solution. I've tried the formula but I'm getting a 'value not found' or 'value not available' error (even with correct, valid values in the referenced cells

All done. Data validation included.

=IF(F4="d",C4+IFERROR(1/(1/(E4*(INT((TODAY()-C4)/E4)))),E4),IF(F4="w",C4+7*INT(DATEDIF(C4,TODAY(),"d")/7)+7,EDATE(C4,E4*(INT(DATEDIF(C4,TODAY(),"m")/(E4)+1)))))

Minor tweak for no renewal date entered:

=IF(C19="","",IF(F19="d",C19+IFERROR(1/(1/(E19*(INT((TODAY()-C19)/E19)))),E19),IF(F19="w",C19+7*INT(DATEDIF(C19,TODAY(),"d")/7)+7,EDATE(C19,E19*(INT(DATEDIF(C19,TODAY(),"m")/(E19)+1))))))

Oh my, you work Excel quickly. Thank you very much for your fixes!
I think there might be a bug with regards to more than one week but it can be circumvented by using the days equivalent of it

Originally Posted by josephteh

Please attach your file.

Hey Joseph, thank you your time. Below is the attached file as requested for your review. Thank you for your help as well. It's greatly appreciated!
Subscription tracker (1).xlsx

Ahh, Excel 2019, version 1808. I will try it out on OFFICE 365, I didn't even think about the Excel version. Thanks for pointing that out!

I have changed the formula slightly by replacing LOOKUP with MATCH, try to hit Ctrl+Shift+Enter (instead of Enter) the array formula.

I pressed Ctrl + Shift + Enter and it worked immediately, what does that do?
I would give you rep but I have already given you one. Thank you so much.

I hadn't seen the subsequent posts. Joseph... you may indeed be correct. A revised version is attached.

=IF(C4="","",IF(F4="d",C4+E4*ROUNDUP((DATEDIF(C4,TODAY(),"d")/(E4)),0),IF(F4="w",C4+E4*ROUNDUP((DATEDIF(C4,TODAY(),"d")/(E4*7)),0)*7,EDATE(C4,E4*(INT(DATEDIF(C4,TODAY(),"m")/(E4)+1))))))