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

Highlighted

@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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies