Excel - Date formula

[cboozer811]

I need to calculate between dates and apply to dollar amount (pro-rate)

This one is correct

4-1-2011 to 4-30-2011 = 30 days - apply to $44,000
J5 K5 =(K5-J5+1)/365)
L5 (pro-rata factor .08219)

$44,000 per 100 x .80 = $352.00 x .08219 = $28.93
I5 M5 N5 05 P5


Because K5 is less than J5 my pro-rata factor is off by one day
L5 pro-rata factor formula need to be the same for all rows (L5 thru L45)

4-1-2011 to 2-7-2011 = 53 days - apply to $44,000
J5 K5 =(K5-J5+1)/365)
L5 (pro-rata factor -.14247= should be -.14521)
52 days 53 days

$44,000 per 100 x .80 = $352.00 x -.14521 = $51.11
I5 M5 N5 05 P5


How can I make the formula in (L5 thru L45) be able to calculate the right amount of days if K5 is less then J5 and if K5 is greater then J5?

 

[Gerald Higgins]

Hi

Your post is pretty confusing, I can't really make sense of it I'm afraid.
BUT, the bit about calculating the difference if K5 is less than J5 AND if K5 is greater than J5. . . there are several ways to do this.
Probably the easiest way is

=abs(k5-j5)

You could also try

=max(k5,j5)-min(k5,j5)

and also

=if(k5>j5,k5-j5,j5-k5)