IF and AND, together

If statements are a pretty powerful tool in your Guru belt. If you know IF, you probably know more than 50% of the people in your office. We've used IF here in conjunction with ISERROR to format your spreadsheet nicely. Today, I want to use IF with AND because I really like these two functions together.

Let's say you work for a company who only pays in increments of 15 minutes. So, if you show up at 2:03, you start getting paid at 2:15. And, if you leave at 5:03, you stop getting paid at 5:00. I know! Corporate America*, right? Geez.


Well, if you work in Corporate America and you're punching a time clock, all of that is already worked out in the payroll software, so you wouldn't really have any need to do this exercise. But, it's the best example I can think of...

The End Result:


Here's the formula that will do it:
=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0),IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0),IF(AND(MINUTE(C2)>15,MINUTE(C2)<=30),TIME(HOUR(C2),30,0),IF(AND(MINUTE(C2)>0,MINUTE(C2)<=15),TIME(HOUR(C2),15,0),C2))))

Well, as they say in corporate America, how do you eat an elephant? One bite at a time! So, let's take this in little elephant chunks.** (Who eats elephants, anyway?)

=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0)

The IF Statement is:
IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59)
IF this is true: the minute is greater than 45 AND less than 59
Return this value:
TIME((HOUR(C2)+1),0,0)
What a neat new function! With the TIME function, the first value represents the hour (in this case my starting hour + 1), the second position represents the minutes, and the third value represents seconds.

If my IF statement was FALSE, return this value:
IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0)

Another IF statement! This is called Nesting IF statements, and you can only nest 7 IF statements in any one formula. But, you can add ANDs and ORs and test more than 7 conditions. Get creative, play with it!

In English, this says if my minute value is between 30 and 45, return the TIME of the hour of my original start time and 45 minutes.

If the value is false, another IF Statement! I think you get the drift. At the end, if none of the conditions are met in the 4 IF statements, then it will return the value of the original start time. If the formula is written right, it should always be the top of the hour so it falls in line with all the Corporate America BS guidelines. I hate that the man is always out to get me!

I did another formula for the end time:

IF(AND(MINUTE(D2)>=45,MINUTE(D2)<59),TIME(HOUR(D2),45,0),IF(AND(MINUTE(D2)>=30,MINUTE(D2)<45),TIME(HOUR(D2),30,0),IF(AND(MINUTE(D2)>=15,MINUTE(D2)<30),TIME(HOUR(D2),15,0),IF(AND(MINUTE(D2)>=0,MINUTE(D2)<15),TIME(HOUR(D2),0,0),D2))))

I actually just dragged over the original formula, and then changed things up a bit so that the workers don't get paid for any minutes they work past the quarter hour until the next quarter hour begins. Take a close read and if you need the English version, let me know...

*I kid about Corporate America! I love Corporate America! Corporate America loves me! I've never seen such a punishing time clock in Corporate America.
**I kid again! I love elephants, but I'm not so sure they love me back.