 • 509K Members
• 6,792 Online
• 606K Conversations

# IF with AND using TIME calculations

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 Start Kronos Clock in Variance in minutes A B C D 6:30 AM 6:25 AM 0:05 7:00 AM 8:11 AM 1:11
2 Replies

# Re: IF with AND using TIME calculations

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

# Re: IF with AND using TIME calculations

One more variant 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.

Related Conversations
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
Time range auto fill colour
Francesc B. in Excel on
2 Replies
Excel If Functions