COMPARING TIMES AND DISPLAYING TEXT USING IF CONDITION

[naga rajan]

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(C1>D1,"LATE",IF(C1<E1,"EARLY",IF(C1=D1,"PERFECT"))).

I get only early or late not perfect when both the times are equal.
Please help out.

 

[joeu2004]

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(C1>D1,"LATE",IF(C1<E1,"EARLY",IF(C1=D1,"PERFECT"))).

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(C1>D1,"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")

 

[naga rajan]

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?

 

[joeu2004]

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