Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sanscull
 
Posts: n/a
Default subtraction of times, convert & multiply by a conditioned rate

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
  #2   Report Post  
sanscull
 
Posts: n/a
Default

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






  #3   Report Post  
sanscull
 
Posts: n/a
Default

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








  #4   Report Post  
PC
 
Posts: n/a
Default

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



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










  #5   Report Post  
PC
 
Posts: n/a
Default


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





  #6   Report Post  
PC
 
Posts: n/a
Default

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





  #7   Report Post  
PC
 
Posts: n/a
Default

"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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert text & multiply Boenerge Excel Discussion (Misc queries) 2 May 7th 05 08:47 PM
convert text & multiply Boenerge Excel Worksheet Functions 1 May 7th 05 08:20 PM
CONVERT Function Disappered in Excel Gord Dibben Excel Discussion (Misc queries) 3 April 13th 05 07:59 PM
how can I multiply a 45-vector times a 45*45 matrix? jortorzor Excel Discussion (Misc queries) 2 March 24th 05 03:11 PM
How do I convert times from hours to minutes? kkrebs Excel Discussion (Misc queries) 5 December 30th 04 07:13 PM


All times are GMT +1. The time now is 10:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"