Recurring billing fee

[Closed]
Report
Posts
11
Registration date
Wednesday January 30, 2013
Status
Member
Last seen
February 5, 2013
-
Posts
11
Registration date
Wednesday January 30, 2013
Status
Member
Last seen
February 5, 2013
-
Hello all,

I was wondering if anyone knows a way to have Excel add a monthly billing charge to invoices. I need something that would automatically take the amount the patient owes and add a billing charge to it each month on a certain day only. I am able to set up a function that can do it for one month, but I can't seem to get it to recur every month and add that fee on top of the last month's total bill. Any ideas would be greatly appreciated.
Thank you in advance!


4 replies

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Rawr,

An idea could be that when excel is opened on a specific day of the month, that a value is added to a specified cell. Problem is that the file should not be opened multiple times on that specific day or the value will be added multiple times.

There are more options but more details would be nice to find the right solution.

But shouldn't the billing charge be visible on the invoice?

Best regards,
Trowa
Posts
11
Registration date
Wednesday January 30, 2013
Status
Member
Last seen
February 5, 2013

Thank you Trowa,

I have tried that and you are correct. It will add it multiple times. Fortunately, I am the only one that will be messing with any of the invoices, so I can mass open them all on that one day and then they will update and I will print them off to send off.

That is another problem I am having. I really need the invoice to reflect exactly what each patient is being charged for each month. It's very time consuming going going into each invoice and updating each of them manually. I am aware that there is actually billing software out there that can do these things for me, but my boss is pretty set on using excel since they have been using it for years, so I am working with what I am given.

I really appreciate your help,
Rawr
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Rawr,

Well if every invoice is unique then there is not much you can do but to adjust them manually.

If some invoices need the same line of data, you could write a code which applies to the currently opened files, to speed things up a bit.

Please provide more info if further assistance is needed.

Best regards,
Trowa
Posts
11
Registration date
Wednesday January 30, 2013
Status
Member
Last seen
February 5, 2013

Hi Trowa,

They are all unique as of right now, but I just set up a general layout that I can use for each one. Of course, now I have to go and apply that to every old invoice. But at least in the future I will not have to mess with them too much. I just wanted to see if there was a way I could get a cell to add that amount to their total each month before I bother wasting my time and redoing all theses invoices.

So far I have gotten everything to update automatically each month except that added billing charge. Mostly because it either wants to add it multiple times or I cant get it to generate a statement showing it was a billing charge when it adds the amount to their total. I am starting to wonder if I am even going to be able to do that or if this is something that I will just have to keep doing every month.

I was thinking that I could go ahead and have their total added at the end and then in the layout of the page, have one cell that always says "Billing Charge" and out beside that one, I could have the actual amount of the billing charge already there as well. That way I can drop down and find a formula that will add the cell with their total and the cell with the set billing amount together. So far that's the only way I have found that I can always show exactly what their billing charge is.
But that seems to only work for the first month. After the first billing charge is added and they have their total amount due, I am not sure how to get the next months bill or "Total" to reflect the billing charge that was previously added and then add another billing charge on top of that.

Now whit all of that said, I feel like I may have just made this all sounds really really confusing >.<

Thank you for your help,
Rawr
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Rawr,

So you update the invoice multiple times which could take you to the next month and thus you need an extra billing charge.

There is also the possibility to run a code before the print order is given.
The code could look at the start date of the invoice (i.e. 15-1-2013) compare it with the current date (i.e. 15-3-2013) and add a billing charge for each month on the invoice like:
Billing charge January ... Amount
Billing charge February... Amount
Billing charge March... Amount

Are we still on the same page or did you indeed confuse me. :)

Best regards,
Trowa
Posts
11
Registration date
Wednesday January 30, 2013
Status
Member
Last seen
February 5, 2013

Hi Trowa,

Lol yes, it looks as if we are still on the same page here :D I'm glad I wasn't too confusing for you to understand what I was trying to say. Funny, you said what I was trying to get across... just a lot less words and in simpler terms.
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Haha I guess I can add summarization skills to my resume.
Posts
11
Registration date
Wednesday January 30, 2013
Status
Member
Last seen
February 5, 2013

I do believe that is a very handy skill to have sir.