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.


See More: Calculate OTD

Report •

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 •

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

Report •

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

Ask Question