Calculating values between dates

Please see attachment.

Thank you for your help!

S

Thank you for your prompt response.

That formula does not allow me to find the true expense when the dates I set do not correspond with the billing period.

I hope what I am saying makes sense.

I think I interpreted things a little differently than AlKey? I went with the following:

=(INDEX($A$2:$A$4,MATCH($B$7,$A$2:$A$4,1)+1)-$B$7)*INDEX($D$2:$D$4,MATCH($B$7,$A$2:$A$4,1)+1)+($B$9-INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)

Which returns a value of $137.41. My assumption is that you were looking to combine the pro-rated portions of the two months overlapped by the chosen dates. Fair warning, though, my formula won't work if the dates stretch over 3 pay periods.

Cantosh, your interpretation was correct.

However, the formula doesn't work also when the dates I set belong to one pay period only.

Thanks for your help though.

S

Originally Posted by simonplus

the formula doesn't work also when the dates I set belong to one pay period only.

Good point. Try this one:

=($B$9 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)+((($B$9-$B$7)-($B$9 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1))))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)))

Alkey: I know, and I am sorry for that. But, I am at work and didn't have the time to thoroughly explain the problem I am trying to solve.

Cantosh: Calculations are still off by few dollars. Also when the dates correspond to the range limits or to the upper one only, the formula returns an error.

Feel like we

Thank you all for your help.

S

I suspect the issue might be that it isn't clear which pay period your limit dates correspond to. Should 5/2 be attributed $3.40 or $3.48 for the day?

Similarly, if B9 is 5/25, should the total amount include a charge for 5/25, or just the days up until 5/25?

The period ought to end the day before the following period begins

The period beginning on 4/3 should end on 5/1 - charge $3.48 on 5/1

Hope this helps

Thanks for the clarification. I think some of the miscalculations were due to the fact my earlier formula wasn't correctly accounting for the last day correctly. It was also struggling with ranges contained within one pay period, clearly.

Try the new formula below. I know that it can be shorter, but I had one working formula for ranges within a period, and one working formula for ranges across two periods, so I ultimately just combined the two with an IF clause. If you get miscalculations, please let me know specifically what they are (e.g. "For dates X and Y, I should get $123.45, instead I'm getting $126.85). The formula does not return a value if B9=6/1 because the daily amount for 6/1 would go in D5, which isn't included in your sample.

=IF(INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1))=INDEX($A$2:$A$4,MATCH($B$7,$A$2:$A$4,1)),($B$9+1-INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)-((($B$7-INDEX($A$2:$A$4,MATCH($B$7,$A$2:$A$4,1))))*INDEX($D$2:$D$4,MATCH($B$7,$A$2:$A$4,1)+1)),($B$9+1 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)+((($B$9-$B$7)-($B$9 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1))))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1))))

Well done!

I think it works.

What was throwing me off was the number of days calculated by the datedif formula. The formula isn't counting the day corresponding to one of the range limits.

:D

Yeah, that made things challenging from both an intuitive and formulaic standpoint. If it's within your power, it might make sense to add a column so that you can have: Start Date, End Date, Days, Charge, Avg/Day. Then again... if you've got something that works, sometimes it's best not to meddle. For now, I'm just relieved we got something to work!

Question: what's the function of that +1 in the formula? Does it have to do with including the range limits?

Correct. The +1 includes the end date listed in B9; it treats that date as 'Through B9' rather than 'Until B9'. To Excel, 5/8/2015 - 5/7/15 = 1, when we need that to actually count as two days. I believe I overlooked that contingency in at least one of my failed efforts above.