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

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

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

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

"Where did you learn this?"

Mostly here and that is the formula I use for my client billing :)

Regarding column D

The format of B&C do not matter. The difference between the cell values is
the elapsed time. (XL stores time as fractions of days so noon is 0.5...)

To show the elapsed time select

Format/Cells/Number and in the "Type" box enter

[h]:mm

HTH

PC

"sanscull"
wrote in message ...
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

Nice job PC:

fyi, the way to show elapsed time is under the custom box and chossing h:mm;
in my XL program, I did not even have a type box, something you referred to
in your last post.

Thanks again

Have a great day!

PS I realized the fastest way to do the conditional rate as based on the 3
digit code was to make 3 small-width columns and type in each code in a
different column. Putting the 3 digit code in 1 column seemed undworkable.
Plus, I really wanted to be done with this...


"PC" wrote:

"Where did you learn this?"

Mostly here and that is the formula I use for my client billing :)

Regarding column D

The format of B&C do not matter. The difference between the cell values is
the elapsed time. (XL stores time as fractions of days so noon is 0.5...)

To show the elapsed time select

Format/Cells/Number and in the "Type" box enter

[h]:mm

HTH

PC

"sanscull"
wrote in message ...
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