First, thank you very much. Where did you learn this?

Second, I have a problem:

For Column D you list:
=C1-B1

I input the the time columns (B & C) in military time. They display as
"hr:mm AM/PM" and the box itself reads "mm/dd/yy hh:mm:ss AM/PM"

When I input your function for D it returns a value of 0. Do I need to
input the times differently?

"PC" wrote:

My bad

the "-0.025" causes it to round to the nearest 3 minutes.

To round up use
=CEILING(D1*24,0.05)/24



"PC" wrote in message
...

D: =C1-B1
E: =CEILING(D1*24-0.025,0.05)/24
F: =E1*24 (or you can just eliminate the "/24" in E
G =F1* Use a vlookup here

HTH

PC




"sanscull" wrote in message
...
I have fully explained the issue below. I warn you that it is complex;
you
will really need to know Excel to figure this out. Please note that I
can
change Column G to make it easier but I need the other stuff.

I am sorting a large amount of data in columnar format from different
subjects (see Column A below). In one complex function, I need to
subtract
two time periods (see Columns B & C) using a function (e.g.,
"=TEXT(C6-B6,"h:mm"") to produce a difference (see Column D) in hr:mm
format.
I want to apply an IF:THEN logic so that the difference needs to get
rounded
up to the next 3 minute block (see Column E). Then I need to figure out
how
to convert that hour:mm format into a number (see Columng F) that I can
multiply by a rate (that changes as a function of the coded person).

Column A: subject (XYZ--3 digit blind code)
B: Start Time (11:40 AM)
C: Finish Time (2:00 PM)
D: Difference Time (hr:mm) (2:20--actual time)
E: Scaled Time (hr:mm) (2:21--Rounded to next 3 min block)
F: Converted Time (2.35hr = 2 hours + 21min/60min)
G Rate (as seen in the XYZ code above, rate = X * Y & Z is immaterial
for
rate purposes where if X = 1 then .33, if X = 2 then .5 if X = 3 then
..66
and
if Y = 1 then .9 if Y = 2 then .95)

If you can figure this out, you are an Excel G-d!

Thanks