Prorated Monthly Invoices with variable Billing days.

W

warriorpoet

New Member
Joined
Oct 7, 2010
Messages
4
Hello everyone I'm new here but hopefully I can get some help with this question and of course be happy to help anyone that I can with my limited knowledge.

I need to show monthly invoice charges based on a bill cycle date, a service start date, and a service end date.

I will provide monthly rate for a service (variable on type of service) and will provide the start and end dates (changing for various services) as well as the bill cycle end day (the same for every account but may change from one account to the next so this being a variable in the formula would be great - could be 12th day of month or 1st day of month or whatever as it may be.) I've been able to accomplish the formula and have it work great for monthly invoices as well as total for a given time period EXCEPT the following cases:

The bill cycle does not start on the first of the month, and so the service start day() value may be < or > the billcycle day() value.

The start and end dates may be in the same month which causes an error in the calculation of the "in between months" when calculating the total.

Any ideas? Thanks.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
For first month (prorated) I'm using:

=IF($C3>0,($D3*(1+G3-DAY(E3)))/G3," ")

Works fine as long as bill cycle date is the 1st. G3 has the number of days in the starting month formula. E3 is the start date. D3 is th price, c3 is the product code(if c3 empty don't want to populate any info)

I'm calculating final month with same formula shifted over one to the right, which uses f3 and h3 (end date and end month day count)

For months between I've been using =IF($C3>0,(MOD((MONTH(F3)-MONTH(E3)),12)-1)*D3, " ")

This creates some negative values sometimes but the final pricing always ends up right as long as the bill date is the first. Unfortunately I have to meet the needs of those customers and they don't all use common sense. What I need to do is add ability to change the bill cycle date to days other than the first and implement that into these (or new) formulae.

Please help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Prorating Salary based on hire date and Term date - by month
Replies
4
Views
133
KH Consulting
K
T
  • Question
Prorated by date.
Replies
1
Views
291
awoohaw
awoohaw
megera716
  • Solved
Return one of two dates depending on (numeric) day
Replies
3
Views
203
megera716
megera716
H
  • Question
The number of days per month that fall between two dates
Replies
2
Views
449
helpExcelme
H
J
  • Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
65
Jared016
J

Forum statistics

Threads
1,215,051
Messages
6,122,871
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top