Calculate pro rated value using the number of days of the month enrolled

The attached example contains two key variables: Column A (ENROLL DATE) and a value that represents KWH usage.

I would like to calculate a pro rated KWH based on the date enrolled.

If a customer enrolled March 16 in which there are 31 days the formula will use the KWH for the month of March (the month enrolled) and calculate the pro rated useage based on March having 31 days, but the customer enrolling March 16 (half way through the month).

The formula would need to reference the ENROLL DATE cell and then the enroll date month KWH which are the other columns B through W to do the calculation.

Since next year is a leap year, I would think that the year might need to be a factor when determining the number of days in a month.

I am hoping that this is all clear. Again, I have attached a spreadsheet for this.

Thank you in advance - any help is greatly appreciated.

Change your months in B1:W1 to actual dates and add desired results, even if manually in X column first. That way, we won't guess.

Row 2.... so this customer was using up kWH for 2.5 month before enrolling???

If you make the change I asked above, then use: =SUMPRODUCT(--(MONTH($B$1:$W$1)=MONTH($A2)),--(YEAR($B$1:$W$1)=YEAR($A2)),$B2:$W2)/DAY(EOMONTH($A2,0))*DAY($A2)

Clearly there is some anbiguity in your request. This seems logical to me and is completely different to Paul's effort.

=INDEX($B2:$W2,MATCH(EOMONTH($A2,-1)+1,$B$1:$W$1,0))*DAY(EOMONTH(LOOKUP(A2,$B$1:$W$1,$B$1:$W$1),0))/DAY(A2)

It also requires chnages to the dates in row 1 to make them real dates (see sheet). You were asked to (but didn't...)manually calculate a few results. You will now need to do so to see who (if either) is correct!!

I am talking about the months in B1:W1, not all data

The stuff in row 1 is a date is it not? That's what we're talking about.

It's worse. PLEASE do as you were asked. Calculate manually the expected result for a few rows. Compare with the results in my sheet and using the formula given by Paul.

The data in row 2 and down are kWH. However, they relate to months (DATES) in row 1. The headers need to be converted to REAL dates (not text that simply looks like a date).

Do me a favour. Open the sheet I posted. Does it give you the correct result or not???

Right. That is (I think) what my formula gives. Check it out.

Ah wait . I understand now. Life would be so much easier if you actually supplied us with two or 3 manually calculated results.

My formula will do exactly what you wrote above, the result/number of days in month * by the starting date
=SUMPRODUCT(--(MONTH($B$1:$W$1)=MONTH($A2)),--(YEAR($B$1:$W$1)=YEAR($A2)),$B2:$W2)/DAY(EOMONTH($A2,0))*DAY($A2)

This gives 5785:
=(DAY(EOMONTH(LOOKUP(A2,$B$1:$W$1,$B$1:$W$1),0))-DAY(A2))*INDEX($B2:$W2,MATCH(EOMONTH($A2,-1)+1,$B$1:$W$1,0))/DAY(EOMONTH(LOOKUP(A2,$B$1:$W$1,$B$1:$W$1),0))

However, I think your logic is faulty. Change A2 to 1/2/2018. Is the result really expected to be 13017.

I think you really want this:
=(DAY(EOMONTH(LOOKUP(A2,$B$1:$W$1,$B$1:$W$1),0))-DAY(A2)+1)*INDEX($B2:$W2,MATCH(EOMONTH($A2,-1)+1,$B$1:$W$1,0))/DAY(EOMONTH(LOOKUP(A2,$B$1:$W$1,$B$1:$W$1),0))

Yep. And if you set a date to the last day of the month it gives just 1 day's worth of usage.

Phew!!!

You're welcome!!!