Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text & multiply | Excel Discussion (Misc queries) | |||
convert text & multiply | Excel Worksheet Functions | |||
CONVERT Function Disappered in Excel | Excel Discussion (Misc queries) | |||
how can I multiply a 45-vector times a 45*45 matrix? | Excel Discussion (Misc queries) | |||
How do I convert times from hours to minutes? | Excel Discussion (Misc queries) |