Section 27 Working Backwards Through a Calendar (Calendar Formula 3)

Manufacturing Corner on

Tuesday, November 29, 2022

Working Backwards Through a Calendar (Calendar Formula 3)

To make a pull schedule that works through a calendar, we will need a formula that works backwards, or upwards through the  calendar. Given a job stop and working hours, when will I need to start. Here is the development of the formula from Section 6, and it can be modified by changing just one sign.

With a series of substitutions, the 15 formulas can be condensed into 4 as follows:

Calc1 6 =MATCH(E16,B9:B14)+(MATCH(E16,B9:B14)=MATCH(E16,C9:C14))
Calc2 9 =INDEX(E9:E14,B41,1)-(INDEX(C9:C14,B41,1)-MAX(INDEX(B9:B14,B41,1),E16))
Calc3 2 =MATCH(B42-E17,E9:E14)+1
Job Stop 8.5 =INDEX(C9:C14,B43,1)-INDEX(E9:E14,B43,1)+B42-E17

Labels: Scheduling Excel Basic

Thanks for reading Section 27 Working Backwards Through a Calendar (Calendar Formula 3). Please share...!