Home

IF with AND using TIME calculations

%3CLINGO-SUB%20id%3D%22lingo-sub-823270%22%20slang%3D%22en-US%22%3EIF%20with%20AND%20using%20TIME%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823270%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can%20say%20it%20out%20loud%20but%20am%20struggling%20to%20get%20Excel%20to%20do%20it.%26nbsp%3B%20I%20need%20this%20result%20in%20column%20D...%3C%2FP%3E%3CP%3EIf%20B%3CA%20and%3D%22%22%20c%3D%22%22%20target%3D%22_blank%22%3E5%20min%2C%20then%20C.%26nbsp%3B%20If%20B%26gt%3BA%20AND%20C%26gt%3B7%20min%2C%20then%20C.%26nbsp%3B%20Otherwise%2C%20blank.%3C%2FA%3E%3C%2FP%3E%3CP%3EIf%20the%20formatting%20was%20a%20number%2C%20my%20formula%20works.%26nbsp%3B%20But%20when%20I%20change%20it%20to%20time%20it%20does%20not.%20And%2C%20I%20can%20only%20seem%20to%20get%20one%20half%20of%20the%20formula%20at%20a%20time.%26nbsp%3B%20So%20I%20need%20help%20nesting%20them%20together%20as%20well%20as%20time%20time%20value%20comparison.%3C%2FP%3E%3CP%3E%3CEM%3EHere's%20what%20I%20have%20so%20far...%26nbsp%3B%3DIF(AND(B5%3CA5%3E5)%2CC5%2C%22%22)%3C%2FA5%3E%3C%2FEM%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EShift%20Start%3C%2FTD%3E%3CTD%3EKronos%20Clock%20in%26nbsp%3B%3C%2FTD%3E%3CTD%3EVariance%20in%20minutes%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3A30%20AM%3C%2FTD%3E%3CTD%3E6%3A25%20AM%3C%2FTD%3E%3CTD%3E0%3A05%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3A00%20AM%3C%2FTD%3E%3CTD%3E8%3A11%20AM%3C%2FTD%3E%3CTD%3E1%3A11%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-823270%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823525%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20with%20AND%20using%20TIME%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398614%22%20target%3D%22_blank%22%3E%40CoachLaura25%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%3CSTRONG%3EA%20quick%20introduction%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWe%20know%20that%20Dates%20are%20stored%20as%20numbers%2C%20Day%20one%20in%20Excel%20being%20the%20first%20of%20January%201900.%20Since%20then%20every%20day%20increments%20by%20one.%20Today's%20date%20is%2026%2F8%2F2019%20%26gt%3B%26gt%3B%20%3CSTRONG%3E43703%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWhat%20about%20time%3F%20Time%20is%20a%20decimal%20fraction%20of%20a%20day%3A%201%20Day%20%3D%2024%20Hours%26nbsp%3B%20%26amp%3B%201%20Hour%20%3D%2060%20minutes%3C%2FP%3E%3CP%3ESo%20in%20one%20Day%20we%20have%2024*60%20%3D%20%3CSTRONG%3E1440%20minutes%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EKeeping%20that%20in%20mind%20You%20need%20the%20following%3A%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3E%26nbsp%3BEnter%20time%20properly%20formatted%20in%20columns%20A%2C%20B%2C%20C%20which%20means%20RIGHT%20Aligned%20to%20be%20recognized%20as%20number.%20You%20can%20either%20Type%20the%20AM%20or%20PM%20%2C%20alternatively%20Pre-format%20the%20range%20as%20time.%3C%2FLI%3E%3CLI%3EWhen%20referring%20to%20the%20minutes%20in%20column%20C%20(and%20because%20they%20appear%20formatted%20as%20minutes%20(0%3A06)%20then%20you%20have%20to%20convert%20them%20in%20your%20formulas%20to%20a%20decimal%20fraction%20of%20a%20day%20by%20dividing%20by%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E1440%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3EYour%20Conditional%20statement%20includes%202%20situations%20(which%20means%20an%20%3CSTRONG%3EOR%3C%2FSTRONG%3Efunction)%2C%20for%20each%20situation%20you%20have%202%20conditions%20(which%20means%20an%20%3CSTRONG%3EAND%3C%2FSTRONG%3Efunction)%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIf%20you%20put%20all%20that%20together%2C%20then%20the%20formula%20will%20be%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%3CFONT%20color%3D%22%230000FF%22%3EIF(%3C%2FFONT%3E%3CFONT%20color%3D%22%2300FF00%22%3EOR(%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF6600%22%3EAND%3C%2FFONT%3E(B1%3CA1%3E5%2F1440)%2C%3CFONT%20color%3D%22%23FF6600%22%3EAND%3C%2FFONT%3E(B1%26gt%3BA1%2CC1%26gt%3B7%2F1440)%3CFONT%20color%3D%22%2300FF00%22%3E)%3C%2FFONT%3E%2CC1%2C%22%22%3CFONT%20color%3D%22%230000FF%22%3E)%3C%2FFONT%3E%3C%2FA1%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20tested%20it%20in%20the%20attached%20workbook%20and%20it%20works%20fine.%3C%2FP%3E%3CP%3EYou%20need%20to%20format%20column%20D%20as%20Time%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Eh%3Amm%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823621%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20with%20AND%20using%20TIME%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398614%22%20target%3D%22_blank%22%3E%40CoachLaura25%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20497px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128606i221513D19C3548C8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20to%20show%20just%20number%20of%20minutes%20(Column%20C)%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMINUTE(ABS(A2-B2))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUsing%20it%2C%20variance%20above%20limits%20(Column%20D)%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(C2%26gt%3B5%2B2*(1-SIGN(A2-B2))%2CC2%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EBut%20perhaps%20you%20need%20to%20show%20only%20variance%20above%20limits%20(Column%20E%2C%20not%20using%20columns%20C%20and%20D)%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(MINUTE(ABS(A2-B2))%26gt%3B5%2B2*(1-SIGN(A2-B2))%2CMINUTE(ABS(A2-B2))%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20column%20is%20formatted%20to%20not%20show%20zeros%2C%20%22-%22%20instead.%3C%2FP%3E%0A%3CP%3EThat's%20in%20second%20sheet%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
CoachLaura25
Occasional Visitor

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies