Thread: How to Find which one is greater between 2 times

Hi All,
I am trying to use If condition to find which one is the max time however i am not able to do it.Below is the example



"Login Time
IST" Actual Login Time Discrepancy
23:45 23:53
18:15 18:35
18:15 18:13
19:15 19:14
18:15 18:15

In discrepancy i need to check if actual login time is more time than login time IST then it should subtract actual login time from login time IST or else it should display login time IST .

Note: Actual Login Time is in Time format.

What happens when the times cross midnight - eg "IST" 23:55 and "Actual" 0:30 ?

I suspect you will need to build in some logic to differentiate the above from the likes of example 3, ie:

C2:
=IF(OR($B2>$A2,(HOUR($A2)-HOUR($B2))>12),MOD($B2-$A2,1),$A2)
copied down
format as Time

in the above we calculate the time difference where either Actual exceeds IST or where the hour differences would imply a midnight cross over
(in the latter case the Actual Time precedes the IST Time despite being "greater" in reality)

If that's the case it's simply

C2:
=IF($B2>$A2,$B2-$A2,$A2)

Dates and Times in Excel are just numbers.
Dates being Serial Numbers and Time being decimal (1 equating to 24 hours)
It is the Date Format applied to those Numbers that generates the Date "strings" as you see them - if you format as General you will see the underlying numeric.