Forum Discussion

CoachLaura25's avatar
CoachLaura25
Copper Contributor
Aug 26, 2019

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 StartKronos Clock in Variance in minutes 
ABCD
6:30 AM6:25 AM0:05 
7:00 AM8:11 AM1:11 
Need Help

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 26, 2019

    CoachLaura25 

    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.

  • nabilmourad's avatar
    nabilmourad
    MVP
    Aug 26, 2019

    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

Resources