Forum Discussion
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...
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:
- 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.
- 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
- 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