Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Calculated Times
Hello!
I have a worksheet which will be used by employees to determine their break times in a decimal format (ex: 15 minutes = 0.25 hours). The employees enter their particular start time and end times into two columns and the difference is calculated. The result is then plugged into a VLOOKUP where the decimal value is returned. So far so good. The problem is that Excel does not always calculate one minute to be the exact same serial number. Every once in a while it produces a number which is close to but not exactly the same as another minute calculated serial. Since the VLOOKUP will only return an error on an exact match or a result which is less than the result on a "fuzzy" match up, I need to figure out how to compare different serials (or make them the same serial number for the VLOOKUP). For example: 1:01 pm -1:00 pm = 0.000694444444444553 2:01 pm - 2:00 pm = 0.000694444444444442 3:01 pm - 3:00 pm = 0.000694444444443998 8:01 pm - 8:00 pm = 0.000694444444442999 All times calculated result in one minute of elapsed time, however the serial is different which will throw an exact match completely awry. Is there a function which will assist me in this endeavor? Any help is greatly appreciated. Thanks! Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Calculated Times
All you need is to use the =ROUND function to round off your results to fewer decimal places. 6 places should be sufficient since you only want to measure to the nearest minute. CELL A2 = 9:00 CELL B2 = 9:10 CELL C2 = ROUND(B2-A2,6) -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=557747 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Calculated Times
Thanks. I feel like an idiot now. :-D
Mike CaptainQuattro wrote: All you need is to use the =ROUND function to round off your results to fewer decimal places. 6 places should be sufficient since you only want to measure to the nearest minute. CELL A2 = 9:00 CELL B2 = 9:10 CELL C2 = ROUND(B2-A2,6) -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=557747 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Times To Make Sure No Overlap | Excel Worksheet Functions | |||
PIVOT TABLE - hiding records with CALCULATED item values equal to | Excel Discussion (Misc queries) | |||
reducing the number of times an IF statement needs to be calculated in embedded IF statements | Excel Discussion (Misc queries) | |||
Comparing Times | Excel Discussion (Misc queries) | |||
Comparing Times | New Users to Excel |