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.

Time Calculations

I was working on a timesheet in Excel the other day, and I was surprised to see that 4:00 PM minus 2:00 PM doesn't equal 2 hours. Very surprised indeed...

So, you know what I did? I hit F1. F1 and my husband and my friend Dawn are the three reasons I am the excel guru that I claim to be.

F1 was very helpful, but I took what it told me and expanded my new found knowledge to get it to do what I actually wanted my timesheet application to do. What I wanted my application to do was calculate the elapsed time in hours between a start and end time in one 24 hour period (as in, not to exceed a day).

Here's what I wanted to see:

From             To                 Elapsed Time in Hours
2:00 PM        4:00 PM             2

When I subtracted 2:00 PM from 4:00 PM like this =(B2-A2), here's what I got:
From             To                 Elapsed Time in Hours
2:00 PM        4:00 PM             .0833
When displayed as time, .0833 = 2:00 AM

So, I think I get what it's doing, but it's not what I want.

F1 suggested a few functions, including the hour function. So, I changed my formula to this:
=HOUR(B2-A2)

And that converted .0833 to the number 2. Which was precisely what I wanted in this particular instance.

So, I pulled my formula down, and I discovered that when my start time was 2:00 and me end time was 4:45, my result was still the number 2.

I hit F1 again, and finished reading the help article. It was then that I discovered the MINUTE function.

Using the minute function on this data set:
From To
2:00 PM 4:45 PM
=MINUTE(B2-A2)
Yields this result:
45

So, I put the two functions together in this simple formula:
=HOUR(B2-A2)+(MINUTE(B2-A2)/60)

It now shows me the difference in hours plus the difference in minutes divided by 60.

From                 To                 Elapsed time in hours
2:00 PM            4:45 PM              2.7500

Easy, peasy puddin' pie.

Just keep in mind, this will not work if the time spans more than 24 hours.