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)))