IF with AND using TIME calculations

Copper Contributor

I can say it out loud but am struggling to get Excel to do it.  I need this result in column D...

If B<A AND C>5 min, then C.  If B>A AND C>7 min, then C.  Otherwise, blank.

If the formatting was a number, my formula works.  But when I change it to time it does not. And, I can only seem to get one half of the formula at a time.  So I need help nesting them together as well as time time value comparison.

Here's what I have so far... =IF(AND(B5<A5,C5>5),C5,"")

Shift StartKronos Clock in Variance in minutes 
ABCD
6:30 AM6:25 AM0:05 
7:00 AM8:11 AM1:11 
2 Replies

@CoachLaura25 

Hi

A quick introduction:

We know that Dates are stored as numbers, Day one in Excel being the first of January 1900. Since then every day increments by one. Today's date is 26/8/2019 >> 43703

What about time? Time is a decimal fraction of a day: 1 Day = 24 Hours  & 1 Hour = 60 minutes

So in one Day we have 24*60 = 1440 minutes

Keeping that in mind You need the following:

  1.  Enter time properly formatted in columns A, B, C which means RIGHT Aligned to be recognized as number. You can either Type the AM or PM , alternatively Pre-format the range as time.
  2. When referring to the minutes in column C (and because they appear formatted as minutes (0:06) then you have to convert them in your formulas to a decimal fraction of a day by dividing by 1440
  3. Your Conditional statement includes 2 situations (which means an OR function), for each situation you have 2 conditions (which means an AND function)

If you put all that together, then the formula will be:

=IF(OR(AND(B1<A1,C1>5/1440),AND(B1>A1,C1>7/1440)),C1,"")

I tested it in the attached workbook and it works fine.

You need to format column D as Time h:mm

Hope that helps

Nabil Mourad

@CoachLaura25 

One more variant

image.png

If to show just number of minutes (Column C)

=MINUTE(ABS(A2-B2))

Using it, variance above limits (Column D)

=IF(C2>5+2*(1-SIGN(A2-B2)),C2,"")

But perhaps you need to show only variance above limits (Column E, not using columns C and D)

=IF(MINUTE(ABS(A2-B2))>5+2*(1-SIGN(A2-B2)),MINUTE(ABS(A2-B2)),0)

This column is formatted to not show zeros, "-" instead.

That's in second sheet attached.