Easy Date Calculations When Planning With Excel

by Kien Leong

Calendar Capacity Planning

Capacity planning compares minutes of production with days of resources.  Here is an easy way to convert minutes to days over any working calendar.

This technique also helps to simplify many Excel date calculations for working days and times.

Capacity Comes from a Calendar

It seems that MRP planning processes prefer to manage demand in units of product.  This is rather strange – In the modern world of manufacturing,  resources are managed in time.

Skilled operators are paid by the shift: piece-rate labour pay is a symptom of low-tech, low-skill manufacturing. Machines are mostly owned and amortized over time.   All over the world, operators are hired and capital assets are expensed across days and months.  So, production and capacity planning should have a nice smooth conversion between units and time.

Last week we talked about a simple truth about capacity planning.  I promised to deliver either a capacity planning tool or routing generator that feeds a capacity planner, depending on the preferences.  It looks like these are both very popular, so you can expect to see both tools released in the coming weeks.

This article is intended to give you a little background on the use of date calculations in capacity and production planning.  It will be an important foundation to the capacity planning process.  In fact, managing calendars solves the entire capacity side of the equation.

Rolling Routing Times Up To Calendar Dates

Excel formula involving time and dates are a little trickier than usual calculations.  It is possible that working in base-24 and -60 arithmetic is a little more awkward than our familiar decimals.

We get tripped up with 6-day working weeks, over-time and public holidays. How to create reliable calculations across the working calendar?

Firstly, let’s look at the way Excel stores dates.  As you may know, the native date form in Excel is a count of days that began 1 January 1900.  In Excel terms, this article was published on 40575 (1 February 2011).  Using this single unit, we can represent almost any moment or duration of time.

A shift running from 8.30am – 4.30pm on 1 February has a start time of 40575.3542 and a stop time of 40575.6875.

Now, Excel has functions like NETWORKDAYS (working days between two dates) and WORKDAY (date after a given number of workdays).  This is based on a 5-day week and can accommodate a list of holidays.  It doesn’t work for planning a 6-day week and an attempt to do this with formula can get quite complex. If you have different shift length on different work days, this quickly becomes unworkable.

Simplify Date Calculations and Just Spell It Out

The solution is much simpler.  You define a working calendar for each work center.  A work center could be a pool of labour (e.g. final assembly) or a machine type (CNC milling, SMT assembly, paint) or a production cell (auto assembly 1).

All you need is two columns: Excel Calendars for Capacity PlanningStart and stop of work period.  See this example.  The first two rows show the native form and the rest are formatted in dates and times.

The other three columns are calculations.  The formula are shown above and provide the start date, duration hours and cumulative days. As always, we place the formula in row 8 above the calendar data and paste them into the target cells.

You can have as many records as you like in each day.  A labour calendar could have four or more working periods per day as you factor lunch breaks for each shift.  This is now a definitive source of capacity.  How long production resources are available to make product or changeover.

Prepare the calendar for a period that covers the entire planning period.  You can quickly build a calendar with holidays removed and precise shift lengths for two or three years at a time.  Make changes to overtime and holidays and amend the start/stop columns as you go.

Each Work Center Is Allocated to a Calendar

In a typical factory, work centers perform value-adding work to make products.  Each work center is made up of resources (machines or people) and all resources in a work center operate to the same calendar.  This way, you can have exact shift patterns for each work center.

Define a small number of unique calendar tables (most factories should only need between one and four in total).  Then allocate each work center to the correct calendar.  All capacity calculations come from calculations that relate to the respective calendar table.

Excel Formulas in Calendar Calculations

The MATCH and INDEX  formulas are hugely useful here against the cumulative days column.  If I want to know the cumulative working hours between two dates, I can MATCH the date in question with the start and stop column top find the rows with the closest values, and then deduct the cumulative hours on the starting row from those of stop row.

To get a very detailed explanation of this technique, you can download our Scheduling Excel tutorial.  This runs through production scheduling and capacity planning with calendars.  It contains 43 brief lessons and will certainly answer all possible questions about calculating start-, stop- and working-times through a calendar.  The PS Cycles Integrated Planning System uses this calendar technique and you can download it here (you will need to log in or subscribe for free first).

The capacity planning tool due to released later on this week will also use calendars and this article will be a helpful background.

Specify exact calendars for each group of resources and get precise, reliable calculations for dates and times.  This is a foundation of capacity planning and finite production scheduling.  You will unlock some powerful planning and scheduling capabilities by just using this one calendar technique.

{ 7 comments… read them below or add one }

bala February 14, 2011 at 10:20 am


Eric February 22, 2011 at 10:02 am

what is the value of range E7 ?

Kien Leong February 22, 2011 at 10:15 am

E7 is blank. The reference to E7 is to identify the headings row when the formula is pasted down into range $E$11:E$17.

Column E is a cumulative quantity, so in the first cell E11, the total is just D11. Every subsequent cell the total is adding the value from the cell above.

The principle behind this method is to set a single formula once and then paste it into every cell in the target column, then replace by values.

Read more about it here: Define logic in one place and in our Dynamic Named Range Tutorials

Frank Clas March 17, 2011 at 2:33 am

This is helpful for many other date calculations. I am a project mgr and am interested in using this in a Gantt Chart. Any info on creating Gantt Charts is appreciated.

Kien Leong April 7, 2011 at 3:28 am

Hi Frank,

Our main Gantt Chart application is in finite scheduling. You can see this in the PS Cycles Integrated Planning System. This generates a Gantt from a list of jobs/tasks with start and finish times.

Alternatively, you can check out Pedro Wave’s content as above. he has some nice tools from the PM side.

Pedro Wave March 31, 2011 at 12:35 pm

Frank, visit my blog searching Gantt Chart.

You could download a Gantt Chart with risk scenarios.

narayan pathak June 3, 2012 at 2:56 am

hourly data collection

{ 3 trackbacks }

Previous post:

Next post: