Comparing Time

[30percent]

Hi,

Subject	Time 1	Time 2	Check Time
Subject 1	04:00	03:00	FALSE

I have formula =TEXT(VLOOKUP(B47,'Paste'!$A:$E,2,FALSE),"HH:MM") in cell B2 and =TEXT(VLOOKUP(A47,'Eastern Time'!$A:$I,3,FALSE),"HH:MM") in cell C2 and =MINUTE(ABS(D47-C47))>1 in cell D2.

I expect cell D2 to return TRUE.
If I make a small change to the formula in D2 to =MINUTE(ABS(D47-C47)) it would result in 0. How could that be? What am I missing?

Thanks

 

[SunnyAlv]

whats value on cell D47 and C47 ?

 

[30percent]

whats value on cell D47 and C47 ?

Sorry the formula should be:

=TEXT(VLOOKUP(A2,'Paste'!$A:$E,2,FALSE),"HH:MM")
=TEXT(VLOOKUP(A2,'Eastern Time'!$A:$I,3,FALSE),"HH:MM")

and the Value on A2 in this example is as shown: Subject 1

 

[Peter_SSs]

I expect cell D2 to return TRUE.

I don't expect it to return TRUE. The difference between 04:00 and 03:00 is 1 hour and 0 minutes. The MINUTES therefore is zero which is not > 1.

Similarly, when you make that small change to the formula it returns 0 as described above.

From the Microsoft Help on the MINUTE function.

Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

BTW, to make it easier for people to help you in the future I suggest that you ..

• Update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’), and
  
  
• Investigate XL2BB for providing sample data & expected results to make it easier for helpers to copy your data for testing and understand just what you have & where it is and also what you want & where it is to be.