Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARING TIMES AND DISPLAYING TEXT USING IF CONDITION
Consider this example.
A1 - start time B1 - end time. C1 - time taken D1 - alloted time E1 - status Consider start time as 9:00:00 and end time as 9:15:30 So the difference is 00:15:30 Alloted time is also 00:15:30 If time taken(C1) is greater than alloted time(D1) i want to print "LATE" in E1. If C1 is less than D1 then print "EARLY" If both times C1=D1 then "PERFECT". The formula I used: =IF(C1D1,"LATE",IF(C1<E1,"EARLY",IF(C1=D1,"PERFEC T"))). I get only early or late not perfect when both the times are equal. Please help out. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARING TIMES AND DISPLAYING TEXT USING IF CONDITION
"naga rajan" wrote:
A1 - start time B1 - end time. C1 - time taken D1 - alloted time E1 - status Consider start time as 9:00:00 and end time as 9:15:30 So the difference is 00:15:30 Alloted time is also 00:15:30 [....] The formula I used: =IF(C1D1,"LATE",IF(C1<E1,"EARLY",IF(C1=D1,"PERFEC T"))). I get only early or late not perfect when both the times are equal. I presume you have a typo only in your posting: C1<E1 should be C1<D1. In any case the formula in E1 should be: =IF(C1D1,"LATE",IF(C1<D1,"EARLY","PERFECT")) There is no need to test C1=D1. It is redundant. Nevertheless, I can duplicate your problem using constants. I wonder if some of the time values are calculated. Presumably C1 is =B1-A1. What about D1? B1 or A1? The point is: Excel times are stored as numeric fractions of a day using 64-bit binary floating-point. And most non-integers cannot be represented exactly. Consequently, it is quite common for infinitesinal differences to creep into Excel calculations with non-integers. The remedy is usually to explicitly round either the calculations or the comparisons. For example, the formula in C1 should be: =--TEXT(B1-A1,"hh:mm:ss") The double-negative converts text to numeric time. Similarly for any other calculated time. Alternatively, the formula in E1 could be: =IF(--TEXT(C1,"hh:mm:ss")--TEXT(D1,"hh:mm:ss"),"late", IF(--TEXT(C1,"hh:mm:ss")<--TEXT(D1,"hh:mm:ss"),"early,"perfect") But as you can imagine, that is inefficent. Alternatively you could write: =CHOOSE(2+SIGN(TEXT(C1,"hh:mm:ss")-TEXT(D1,"hh:mm:ss")), "late","perfect","early") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARING TIMES AND DISPLAYING TEXT USING IF CONDITION
Thank you it helped me.
In between I need to display number and then text in a single cell. Example: A1 = Name B1 = 6 C1 should be 6,Name. Can this be done? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARING TIMES AND DISPLAYING TEXT USING IF CONDITION
"naga rajan" wrote:
In between I need to display number and then text in a single cell. Example: A1 = Name B1 = 6 C1 should be 6,Name. Can this be done? If I understand the question correctly, perhaps the following in C1: =B1 & "," & A1 However, if B1 might not be an integer, you could do the following to control the format: =TEXT(B1,"0") & "," & A1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to count the number of times text occurs based on condition | Excel Discussion (Misc queries) | |||
comparing times | Excel Programming | |||
IF condition using GMT and BST times | Excel Worksheet Functions | |||
Comparing Times in Text Boxes to Times in Cells | Excel Programming | |||
Comparing Times | New Users to Excel |