How to create time interval entries

- [Narrator] In this worksheet, we see two sets of data. First in columns A, B, and C. Then over in columns H, I, and J. Essentially the same, what we'd like to be able to do, and explore different options along the way, is to allow time entries here to be entered in column C initially. And then have them adjusted to be the nearest 10 minutes. That's certainly one approach. It does require additional space on the worksheet. Another approach could be, off to the right, for example in column J, what if we could force the user to make an entry that's got to be an even multiple of 10 minutes.

In other words, a data validation rule that will force the user to type in an entry that must conform to that rule. Different approaches here. Let's first look at columns A, B, C. And the formula that we're going to put in, in column D, now we only want an adjusted time here when there is a time in column C. So if column C is blank here, we want to put in nothing. I'm going to zoom in here, holding down control using the mouse wheel and start a formula. Equal if, and you'll notice over in cell F2 is the time interval that we want to use, that could be within the function itself, it could be external, either way.

We want to first check to see if this cell here is blank and there's a function called isblank. It does exactly what it's supposed to do. It's going to check this cell right here to see if it's blank. Comma, if it is blank, what do we want to display here? Blank, that's double quote double quote, sometimes referred to as the null string, comma. Now what do we wanna do if it's not blank? We wanna take that amount there and round it, but not using the round function.

We wanna use a function called mround. That allows us to round to multiples that are not necessarily decimally oriented. Comma and we've got that amount over in cell F2. But we do wanna be copying this down the column, so that needs to be an absolute address. I'll press the function key F4. And then right parenthesis. And another right parenthesis. So if the cell to the left is blank, let's put in a blank, otherwise let's perform this calculation.

So what we're expecting to see here is that amount rounded to the nearest interval of 10 minutes, it's gonna be 540 and that's what we see. And we'll copy it down, and maybe we'll copy it all the way down, this goes down almost 100 rows or so. You can see what's happening in each case here, this is working and all these we would copy this all the way down to that last cell in the bottom. And since it's not adjacent to other cells, just drag it down to there, there we are. And as we add an entry here, whatever it might be, now we're expecting to see five o'clock and we see it and so on.

So once again, here's that formula. If we didn't have that cell reference in cell F2, which for the moment you can't see, we could put in here, this might surprise you, "0:10". And it worked that way, so that works the same way. I think an external reference is better because, first of all let's see how this is displayed. We could do that, but if I press undo, let me go back to this reference.

Now we have the luxury of saying well let's make this be 15 minute intervals, or five minute intervals. So double click out here to the right and although it looks funny to see this as 12 AM, if I want a five minute interval, make that 05, 15 minute interval, 15. We see the answers, so all these are rounded to the nearest 15 minutes. I think you get the idea. Or let's change it one more time, make it be five minutes. Do you see what's happening there? So that's one approach to this. No matter what the entry is here, obviously want to standardize the display of these.

One approach could be, there is a keystroke shortcut, you can use this too by the way for these columns. It's Control, Shift, at sign. Displays the times that way. Now off to the right, let's approach this differently. And this is gonna be a little bit trickier, but we want a data validation rule that rejects all times, unless they are multiples of 10 minutes. And we want to do a little bit of work ahead of time because this does get a little bit tricky here. And introduce a function that you might not be familiar with, it's called MOD, M-O-D.

We'll just type it in right here. This is a function that does something a little bit unusual. And when you see its description, you say, well how would I ever use that? The MOD function gives us the remainder in a division problem. What if in your warehouse you've got a bunch of items sitting around? This is how many you've got, we don't have them identified here, but we would in a real life situation. We've gotta pack up and move our warehouse. So these have to go into boxes. Our boxes for these items all hold 16 items. We wanna know what happens after we package these up, how many we'll have left in each case.

So what we want to do, in fact, is divide this by 16. Tell me the remainder and that's what the MOD function will do. 16, there we are, and double click. We've got eight left over there, in some cases maybe zero. There it is, of course that's evenly divisible by 16. Might have some others in there, so is that. So this is how many we have left over. So you're probably wondering what's that got to do with everything we're doing over here. I've got a bunch of times right here. How can I analyze these to find out if they're evenly divisible by 10 minutes? Let's start with this idea here, =mod(, remember this is the function that calculates the remainder, let's take this amount here comma, and what do we want to divide it by? We wanna divide it by 10 minutes.

Now I've got that over there in another cell to the left. Maybe I'll just type it because we see it there. Right now it's in F2. Let's first do it manually. 0: let's make it 10 minutes and Enter. That's off by nine, let's drag this down. There we get a zero. Now if we could use this as a data validation rule, we could check to see whenever this is true and when it is, fine that's okay.

But if it's not, make another entry until it is correct. So that's zero, we see this is zero. But here's an oddity about Excel. If you deal with extremely large numbers or extremely small numbers, sometimes Excel is not quite perfect. Is that really zero? How can we find out? Let's check out what these values are really equal to. And we can change this to general format, Home tab. There is a keystroke shortcut, you could press Control Shift tilde. Or you could just switch this to general format. And as it turns out, that is an extremely small number, but it's not zero.

Now what is it about? I mean, here's a number that's equivalent to two minutes. What if it's a one minute difference? That's a pretty low number too. So we might be able to do this if we can somehow check to see if it's below a certain very, very tiny number. This is making things a little bit obscure here. Now earlier I had suggested maybe we can tie this to the data over in cell F2. Which is currently set at five minutes. So let's rewrite this. If we want this to be a five minute interval, instead of all this here, I'll just type in the F2.

I could certainly go click on the two, cell F2. And that two we'd want to be an absolute address, just to check this out. Now we're getting time entries here and we're back to the display like this. But when we start checking with data validation, we need to be checking not for something equal to zero or 0:00, we've got to make sure that it's below a certain amount. So putting all those pieces together, here's what we'd like to do. A data validation rule here that prevents any entry here, unless it's an even multiple, let's say 10 minutes, five minutes, whatever.

I'll change this back to 10 'cause that's what we started with earlier. And again, looks kind of funny to see the AM, the time frame there. 12:15, well 10 AM, but that's what we use. So we want a data validation rule for column J. It's not uncommon when you set up data validation rules to select the entire column. And although a minor point, at some point in the future, come back and simply get rid of the rule for this cell only. But it's easiest when we set up the formula to select column J data tab, data validation, and ironically we don't use the time option here.

Now this is viable in some cases to make sure that an entry truly is a time. But that's all we can really do with it. And the time within a certain interval perhaps too, but what we're trying to do involves using a custom formula, so we choose Custom. And here's the formula, =mod, I'm gonna type this in uppercase, although that's not truly necessary. I'm also going to display this larger in a moment. I'll copy this and display it on the worksheet. And I use the cell J1 even though literally we will not be putting in any formula there.

When I selected column J, J1 is the active cell. So the formula begins with a reference to cell J1 comma and now we need to put in that cell reference. It's going to be F2. Now I can just slide over to the left and click it if I wish. Make sure that's an absolute address. So press the function key F4. Right parenthesis, now we'd like to be able to say make this equal to zero. But Excel, in dealing with extremely large or extremely small numbers, it's never quite equal to zero in some cases.

So we need to put in here not equal to, but we're looking for a number when the remainder is less than point, and I'll just put in five or six zeros. One, two, three, four, five. That's good enough, one. Again that seems strange. I'm going to highlight that and press control C, so you can see it larger. Then click OK, and this throws people when you get a rule like this. Now because I selected column J, it's simply telling me this doesn't work, but I do want to continue. So I'll put in the time right here, how about 2:49.

That's not an even multiple of 10 minutes, so we get a message. I'll retry and I'll try 2:50. And we put in that entry. And that works, okay. Let's try another one. 3:00, by the way you can enter data as a time like this, that's a valid entry. It will display with an AM PM, so might wanna reconsider that. But that is valid and it should work here, and it does. And how about 3:44? That should not work, and it doesn't. So the formula, it's in place there.

I'll paste it right here and you don't have to do this of course, but it's certainly good for documentation purposes, is this. Let me just center this here so we can see it better from the Home tab. There we go, so that's the validation formula. Took a while to get there so to speak, but this is working properly. So the approach here is we don't have a separate column, we simply force the user to type in a time that's an even multiple of 10 minutes. And if we change this amount over here to be something different, we've changed the rule. And that's something that's a little risky as you work a data validation, because if the data validation rule changes, existing entries do not change.

That's not an even multiple of 15 minutes, so you wanna be careful with that idea. So recapping just briefly here. We've seen an approach here using data validation that forces the user to put in an entry, it's gotta be a multiple of, in our example here 10 minutes. Then over here we saw another situation where the user simply makes an entry, a formula to the right, adjust it to the nearest 10 minutes.