Need help creating a formula!

[kristylee]

I need to be able to take an invoice amount for a range of dates and allocate it according to defined periods. For example:

Period 1 = 9/13/20 and prior
Period 2 = 9/14/20 through 10/20/20
Period 3 = 10/21/20 through 11/30/20
Period 4 = 12/1 and onward

So let's say I have an invoice for $5,000 covering 3rd Q 2020 (7/1/20 - 9/30/20) then the calculation would allocate 75 days of it to Period 1, 17 days to Period 2 and 0 to periods 3 & 4. Any ideas?? I am trying but can't figure out anything simple.

 

[kristylee]

anyone have a suggestion for this?

 

[Peter_SSs]

We have no idea what your worksheet looks like and are not familiar with what you are trying to do. So what about a small set of sample data and the expected results with XL2BB?

Also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)