# Calculate OTD September 26, 2010 at 22:40:24
Specs: Windows XP
 I am trying to create a formula to represent OTD (%) on the following values:M5 = Date Returned (Actual) L5 = Promised Return Date S5 = Variance (in Days)If the Date Returned (Actual) equals the Promised Return Date then OTD is 100% but if the Date Returned is greater than the Promise Return Date by 1 day then 10% is subtracted per each day late. I've created the following formula using only the Variance but if I format the column with percentages excel is multiplying each cell by 100 and I am curious to see what the formula would look like using M5 and L5.=IF(S5<=0,100,100-S5*10) See More: Calculate OTD

#1 September 27, 2010 at 04:41:21
 Well, it seems to me that if the final result is being multiplied by some factor when the % formatting is applied, it would make sense to divide each number in the formula by the same factor.Or you could just use percentage signs right in the formula.Give those a shot and let us know how they work for you.P.S. What happens if the item is returned 11 days late?

Report •

#2
September 27, 2010 at 17:03:38
 You’re a genius, again thank you!!!!

Report •

#3
September 27, 2010 at 17:05:12
 I forgot...if returned 11 days the result is zero. I'm using a scorecard based on 1-10.

Report •
Related Solutions 