# Calculating values between dates

1. ## Calculating values between dates

S  Register To Reply

2. ## Re: Calculating values between dates

Try this formula
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Calculating values between dates

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.  Register To Reply

4. ## Re: Calculating values between dates

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.  Register To Reply

5. ## Re: Calculating values between dates

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

S  Register To Reply

6. ## Re: Calculating values between dates Originally Posted by simonplus S
You gave us no explanation of what you need. I don't see how you can expect to receive a satisfactory answer to your problem.  Register To Reply

7. ## Re: Calculating values between dates 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)))  Register To Reply

8. ## Re: Calculating values between dates

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  Register To Reply

9. ## Re: Calculating values between dates

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?  Register To Reply

10. ## Re: Calculating values between dates

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  Register To Reply

11. ## Re: Calculating values between dates

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))))  Register To Reply

12. ## Re: Calculating values between dates

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  Register To Reply

13. ## Re: Calculating values between dates

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!  Register To Reply

14. ## Re: Calculating values between dates

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

15. ## Re: Calculating values between dates

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.  Register To Reply