Aug 26 2019 11:35 AM
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 |
Aug 26 2019 01:32 PM - edited Aug 26 2019 01:33 PM
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:
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
Aug 26 2019 03:00 PM
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.