subtraction of times, convert & multiply by a conditioned rate

[Guest]

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

 

[Guest]

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?

 

[Guest]

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...

"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

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]

The formula for the lookup is something like. Change the AA,BB...
references and the 1,2... to meet your needs.

=VLOOKUP(A1,{"AA",1;"BB",2;"CC",3},2,FALSE)

The way to show elapsed time I provided is correct. If you use h:mm
withouth the []'s you will get an error if time goes over one day as the
format will only show the hour portion of the time elapsed. While that may
not be a situation you anticipate for any one session, if you were to show
the total of session times the amount could very well be greater than 24
hours and the time displayed will not be correct.


PC

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...

"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" <[email protected]>
wrote in message news:[email protected]...

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?

:

My bad

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

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




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




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

 

[PC]

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

 

[PC]

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]

"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