Formula to allocate value by date per day...

[Brakwa]

Hello, I hope someone can help.

I have a start date and end date with a value associated those dates. I want to create a monthly view showing the value if a date is between the start and stop date.

cell B3 contains the "Start date" 10/2/18, cell C3 contains the "End Date" 10/5/18, and cell D3 will have the value 300.

I want to have the dates in a column of Oct 1 through Oct 31 say in column A and for the dates 10/2 through 10/5 I want the result to be the value 300 in the cells next to the start and end date. I have tried to use sumifs with greater data in column C and with a second criteria data less than data in column d each one works individually but for some reason the 2 together delvers a 0 in all cases.

 

[dUBBINS]

Hello, I hope someone can help.

I have a start date and end date with a value associated those dates. I want to create a monthly view showing the value if a date is between the start and stop date.

cell B3 contains the "Start date" 10/2/18, cell C3 contains the "End Date" 10/5/18, and cell D3 will have the value 300.

I want to have the dates in a column of Oct 1 through Oct 31 say in column A and for the dates 10/2 through 10/5 I want the result to be the value 300 in the cells next to the start and end date. I have tried to use sumifs with greater data in column C and with a second criteria data less than data in column d each one works individually but for some reason the 2 together delvers a 0 in all cases.

Let's say your dates are in A5:A#

In B5, place:

=IF(AND(A5>=$A$3,A5<=$B$3),$C$3,"")

And drag it down.

Does that do what you are asking?

 

[Brakwa]

Thank you very much for the quick response and this works great for one entry. If I have multiple entries however i do not know what to do. How would i have 10/1/18 through 10/31/18 look at multiple inputs of dates. For example:

10/02/18 - 10/05/18 @ 300
10/07/18 - 10/11/18 @ 600
10/15/18 - 10/22/18 @ 800

- and there may be multiple transactions like this in the month.

 

[dUBBINS]

Would the date ranges ever overlap?

 

[Brakwa]

Yes. There is a massive transaction at the beginning of the month for the entire month. I was just going to add that separately. But if there is a way, awesome!!

- 10/1/18 - 10/31/18 @ 2,500
- 10/1/18 - 10/4/18 @ 300
and you understand the rest.

We purchase a certain amount of natural gas for process and try to balance by months end. There have been occasions where some transaction overlap within the month as well.