A question from a recent seminar involved calculating how many unused meals occurred during a month. The person had to rewrite several formulas every month depending on the total number of days in the month. In Episode 756, we'll take a look at some changes to allow that formula to work for every month.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
It's spring seminar season now. I have been doing a lot of seminars and a great question came in last week.
The person worked at a residential care facility and they kept track of how many meals the residents used during the month.
Residents are supposed to get 2 meals a day, you know, but sometimes they go to visit family, they don't eat that meal, and the rule is you can roll over up to 2 unused meals, and so they had a formula that was basically subtracting the number of meals used from 60 and they said, you know, a couple of problems.
Number one, we don't want the roll over to ever be negative.
If somehow they managed to get 61 meals this month, we don't want to short them next month, but we also don't let them roll over more than 2.
That's just the rule of the place, and then the thing that was driving them crazy was, they said, we have to change the formula depending on how many days there are in the month and, you know, it's a complicated spreadsheet -- more complicated than what I recreated here -- and so they basically have hard-coded in the number 60 here in the formula.
Well, the first thing I want to do -- I saw the heading up there in A1 -- I said, well, let's take a look at that heading, and, as you can see in the formula bar, it's just text.
I said we want to change that.
Let's put an actual date in there, 4/1/2008, and then we'll go format that cell.
CONTROL+1 is a great way to get to FORMAT CELLS, and we'll create a CUSTOM number format with 4 Ms and then 4 Ys.
So, it actually now looks exactly like they had it typed but the real difference is we have a date there and we can do some math with that date.
Okay.
Instead of hard-coding 60, I want to put in what I said was going to be a fairly difficult thing to do.
We're going to do =EOMONTH of cell $A$1, 0 months later, and that's basically going to give me the last day of the month.
Now, what I need to get is the day of that.
So, we have the day of that whole big thing.
Now, what we're going to do is * that times 2 because they get 2 meals a day.
So, in April, that's going to give me 60, but if we would switch over to May, 5/1/2008, you'll see that that now it gives me 62 because there's 31 days in May.
[ =DAY(EOMONTH($A$1,0))*2-B4 ] Alright.
Let's go back and attack this formula now.
A couple of rules they had.
It can never go less than 0, they said.
Well, any time that it can never go less than 0, I take the whole formula exactly as it's working and I ask for the MAX.
Now, with the MAX of that formula…and at the end I put ,0.
That way, if we ever go negative, it will give me the 0 instead.
Let's copy that down and see how we're doing.
Okay.
So there.
All my negatives just went away.
[ =MAX(DAY(EOMONTH($A$1,0))*2-B4,0) ] The other thing they said, it can never be greater than 2.
That’s the rules of the facility.
So, again there, I'm going to take the whole formula that I have working, add MIN, ( of that whole formula, and then, at the end, put ,2.
That way, if the calculation happens to give me a number of 3, they missed 3 meals, it will make sure that they only roll over 2.
[ =MIN(MAX(DAY(EOMONTH($A$1,0))*2-B4,0),2) ] Now, at this point, we're all set.
All we have to do, as we get to May, is simply type the new date up in cell A1 and all of the formulas will automatically be rewritten.
They won't have to spend all the time rewriting the formulas again and again and again.
Great question, of course not straightforward to solve.
It required a few different steps.
The big one is using EOMONTH and changing that text date in cell A1 to a real date.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
It's spring seminar season now. I have been doing a lot of seminars and a great question came in last week.
The person worked at a residential care facility and they kept track of how many meals the residents used during the month.
Residents are supposed to get 2 meals a day, you know, but sometimes they go to visit family, they don't eat that meal, and the rule is you can roll over up to 2 unused meals, and so they had a formula that was basically subtracting the number of meals used from 60 and they said, you know, a couple of problems.
Number one, we don't want the roll over to ever be negative.
If somehow they managed to get 61 meals this month, we don't want to short them next month, but we also don't let them roll over more than 2.
That's just the rule of the place, and then the thing that was driving them crazy was, they said, we have to change the formula depending on how many days there are in the month and, you know, it's a complicated spreadsheet -- more complicated than what I recreated here -- and so they basically have hard-coded in the number 60 here in the formula.
Well, the first thing I want to do -- I saw the heading up there in A1 -- I said, well, let's take a look at that heading, and, as you can see in the formula bar, it's just text.
I said we want to change that.
Let's put an actual date in there, 4/1/2008, and then we'll go format that cell.
CONTROL+1 is a great way to get to FORMAT CELLS, and we'll create a CUSTOM number format with 4 Ms and then 4 Ys.
So, it actually now looks exactly like they had it typed but the real difference is we have a date there and we can do some math with that date.
Okay.
Instead of hard-coding 60, I want to put in what I said was going to be a fairly difficult thing to do.
We're going to do =EOMONTH of cell $A$1, 0 months later, and that's basically going to give me the last day of the month.
Now, what I need to get is the day of that.
So, we have the day of that whole big thing.
Now, what we're going to do is * that times 2 because they get 2 meals a day.
So, in April, that's going to give me 60, but if we would switch over to May, 5/1/2008, you'll see that that now it gives me 62 because there's 31 days in May.
[ =DAY(EOMONTH($A$1,0))*2-B4 ] Alright.
Let's go back and attack this formula now.
A couple of rules they had.
It can never go less than 0, they said.
Well, any time that it can never go less than 0, I take the whole formula exactly as it's working and I ask for the MAX.
Now, with the MAX of that formula…and at the end I put ,0.
That way, if we ever go negative, it will give me the 0 instead.
Let's copy that down and see how we're doing.
Okay.
So there.
All my negatives just went away.
[ =MAX(DAY(EOMONTH($A$1,0))*2-B4,0) ] The other thing they said, it can never be greater than 2.
That’s the rules of the facility.
So, again there, I'm going to take the whole formula that I have working, add MIN, ( of that whole formula, and then, at the end, put ,2.
That way, if the calculation happens to give me a number of 3, they missed 3 meals, it will make sure that they only roll over 2.
[ =MIN(MAX(DAY(EOMONTH($A$1,0))*2-B4,0),2) ] Now, at this point, we're all set.
All we have to do, as we get to May, is simply type the new date up in cell A1 and all of the formulas will automatically be rewritten.
They won't have to spend all the time rewriting the formulas again and again and again.
Great question, of course not straightforward to solve.
It required a few different steps.
The big one is using EOMONTH and changing that text date in cell A1 to a real date.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
