• 461K Members
• 5,736 Online
• 559K Conversations

## IF with AND using TIME calculations

Highlighted
Occasional Visitor

# 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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies