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?
Non-editable pictures are of NO USE. Please upload a sample EXCEL file here, that meets our guidleines (see yellow banner - top of screen).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
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.
Last edited by Glenn Kennedy; 02-13-2023 at 12:48 AM.
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!
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.
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?
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.
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.
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.
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.
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
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
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
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
Bookmarks