Look Who it Was Random video

Excel - Effortlessly Calculate Unused Meals per Month in Excel - Episode 756

791 views · Published 5 February 2009 · 4:00 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Calculating Unused Meals per month based on number of days in the month.

Welcome back to the MrExcel netcast! I'm Bill Jelen and it's spring seminar season. I've been doing a lot of seminars and a great question came up last week. The person worked at a residential care facility and they were keeping track of how many meals the residents used during the month. The rule was that residents were supposed to get 2 meals a day, but sometimes they would go visit family and not eat a meal. The facility had a rule that residents could roll over up to 2 unused meals, but they were running into some problems with their formula.

The first issue was that they didn't want the roll over to ever be negative. If a resident somehow managed to get 61 meals in a month, they didn't want to short them the next month. But they also didn't want residents to roll over more than 2 meals. The second issue was that they had to change the formula depending on how many days were in the month, which was becoming a time-consuming and complicated task. So, I decided to recreate their spreadsheet and find a solution.

The first thing I noticed was that the heading in cell A1 was just text. I suggested changing it to an actual date, 4/1/2008, and formatting it with 4 Ms and 4 Ys. This way, we could do some math with the date. Instead of hard-coding the number 60 in the formula, I used the EOMONTH function to get the last day of the month and then multiplied it by 2 since residents get 2 meals a day. This way, the formula would automatically adjust for different months.

Next, I addressed the issue of the roll over never being negative. I used the MAX function to ensure that the result would never be less than 0. And for the rule of not rolling over more than 2 meals, I used the MIN function to limit the result to a maximum of 2. This way, if a resident missed 3 meals, the formula would still only roll over 2 meals.

Now, the facility can simply change the date in cell A1 and all the formulas will automatically adjust. This saves them time and eliminates the need to rewrite the formulas every month. It was a great question and required a few different steps to solve, but the key was using the EOMONTH function and changing the text date to a real date. Thanks for watching and be sure to tune in for more netcasts from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction to the topic
(00:18) The question from a residential care facility
(00:28) The issue with the formula
(00:43) The three problems with the formula
(01:12) Changing the heading to a date
(01:45) Using EOMONTH to get the last day of the month
(02:25) Solving the formula with MAX and MIN functions
(03:16) Automatically updating the formula for future months
(03:29) Conclusion and thanks for watching

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Automatically updating formulas in Excel with changing dates
Changing formula based on number of days in the month
Converting text date to real date in Excel
Excel formula for tracking unused meals at a care facility
Formatting cells in Excel using CONTROL+1
Limiting roll over to 2 meals in Excel formula
MrExcel netcast tutorial on meal tracking at care facilities
Preventing negative roll over in Excel formula
Using EOMONTH function in Excel
Using MAX function to prevent negative values in Excel
Using MIN function to limit values in Excel
YouTube video tutorial on tracking meals at a care facility

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152127/



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.

More from this channel