Invoice forecasting with variable dates

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi

I suppose you can use in your formula

Formula:

Please Login or Register  to view this content.

to get the number of days for the month date or

Formula:

Please Login or Register  to view this content.

if you use the advance month.

Originally Posted by Pepe Le Mokko

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Apologies. Sample workbook now attached.

I'm trying to think of a formula I can enter into column H (Invoice amount) that will run off column D (Return dates), so that when those dates are altered the invoice amount automatically updates.

If you work from the bottom of column H upward, you can see the current formula I have used. For example, in December if there are only a few vehicles of the fleet reminaing, to be returned on the 4th of December, we will only be invoiced in October (we get invoiced two months in advance) for them for four days out of the month (4/31).

This is then made slightly more complicated when multiple vehicles are returned on various dates in the month, as shown in September's invoice, for example.

Is there a more sensible way of doing this other than manually entering the formulas I have done as shown in the attached?