Discussion Re: IF with AND using TIME calculations in Excel
https://techcommunity.microsoft.com/t5/excel/if-with-and-using-time-calculations/m-p/823621#M39173
<P><LI-USER uid="398614"></LI-USER> </P>
<P>One more variant</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 497px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/128606i221513D19C3548C8/image-size/large?v=1.0&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>If to show just number of minutes (Column C)</P>
<LI-CODE lang="markup">=MINUTE(ABS(A2-B2))</LI-CODE>
<P>Using it, variance above limits (Column D)</P>
<LI-CODE lang="markup">=IF(C2>5+2*(1-SIGN(A2-B2)),C2,"")</LI-CODE>
<P>But perhaps you need to show only variance above limits (Column E, not using columns C and D)</P>
<LI-CODE lang="markup">=IF(MINUTE(ABS(A2-B2))>5+2*(1-SIGN(A2-B2)),MINUTE(ABS(A2-B2)),0)</LI-CODE>
<P>This column is formatted to not show zeros, "-" instead.</P>
<P>That's in second sheet attached.</P>Mon, 26 Aug 2019 22:00:49 GMTSergei Baklan2019-08-26T22:00:49ZIF with AND using TIME calculations
https://techcommunity.microsoft.com/t5/excel/if-with-and-using-time-calculations/m-p/823270#M39171
<P>I can say it out loud but am struggling to get Excel to do it. I need this result in column D...</P><P>If B<A AND C>5 min, then C. If B>A AND C>7 min, then C. Otherwise, blank.</P><P>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.</P><P><EM>Here's what I have so far... =IF(AND(B5<A5,C5>5),C5,"")</EM></P><TABLE><TBODY><TR><TD>Shift Start</TD><TD>Kronos Clock in </TD><TD>Variance in minutes</TD><TD> </TD></TR><TR><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR><TD>6:30 AM</TD><TD>6:25 AM</TD><TD>0:05</TD><TD> </TD></TR><TR><TD>7:00 AM</TD><TD>8:11 AM</TD><TD>1:11</TD><TD> </TD></TR></TBODY></TABLE>Mon, 26 Aug 2019 18:35:29 GMThttps://techcommunity.microsoft.com/t5/excel/if-with-and-using-time-calculations/m-p/823270#M39171CoachLaura252019-08-26T18:35:29ZRe: IF with AND using TIME calculations
https://techcommunity.microsoft.com/t5/excel/if-with-and-using-time-calculations/m-p/823525#M39172
<P><LI-USER uid="398614"></LI-USER> </P><P>Hi</P><P><STRONG>A quick introduction:</STRONG></P><P>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 >> <STRONG>43703</STRONG></P><P>What about time? Time is a decimal fraction of a day: 1 Day = 24 Hours & 1 Hour = 60 minutes</P><P>So in one Day we have 24*60 = <STRONG>1440 minutes</STRONG></P><P><STRONG>Keeping that in mind You need the following:</STRONG></P><OL><LI> 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.</LI><LI>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 <FONT color="#FF0000"><STRONG>1440</STRONG></FONT></LI><LI>Your Conditional statement includes 2 situations (which means an <STRONG>OR</STRONG> function), for each situation you have 2 conditions (which means an <STRONG>AND</STRONG> function)</LI></OL><P>If you put all that together, then the formula will be:</P><P><STRONG>=<FONT color="#0000FF">IF(</FONT><FONT color="#00FF00">OR(</FONT><FONT color="#FF6600">AND</FONT>(B1<A1,C1>5/1440),<FONT color="#FF6600">AND</FONT>(B1>A1,C1>7/1440)<FONT color="#00FF00">)</FONT>,C1,""<FONT color="#0000FF">)</FONT></STRONG></P><P>I tested it in the attached workbook and it works fine.</P><P>You need to format column D as Time <FONT color="#FF0000"><STRONG>h:mm</STRONG></FONT></P><P>Hope that helps</P><P>Nabil Mourad</P>Mon, 26 Aug 2019 20:33:46 GMThttps://techcommunity.microsoft.com/t5/excel/if-with-and-using-time-calculations/m-p/823525#M39172nabilmourad2019-08-26T20:33:46ZRe: IF with AND using TIME calculations
https://techcommunity.microsoft.com/t5/excel/if-with-and-using-time-calculations/m-p/823621#M39173
<P><LI-USER uid="398614"></LI-USER> </P>
<P>One more variant</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 497px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/128606i221513D19C3548C8/image-size/large?v=1.0&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>If to show just number of minutes (Column C)</P>
<LI-CODE lang="markup">=MINUTE(ABS(A2-B2))</LI-CODE>
<P>Using it, variance above limits (Column D)</P>
<LI-CODE lang="markup">=IF(C2>5+2*(1-SIGN(A2-B2)),C2,"")</LI-CODE>
<P>But perhaps you need to show only variance above limits (Column E, not using columns C and D)</P>
<LI-CODE lang="markup">=IF(MINUTE(ABS(A2-B2))>5+2*(1-SIGN(A2-B2)),MINUTE(ABS(A2-B2)),0)</LI-CODE>
<P>This column is formatted to not show zeros, "-" instead.</P>
<P>That's in second sheet attached.</P>Mon, 26 Aug 2019 22:00:49 GMThttps://techcommunity.microsoft.com/t5/excel/if-with-and-using-time-calculations/m-p/823621#M39173Sergei Baklan2019-08-26T22:00:49Z