Calculating the deadline

nasrundin

New Member
Joined
Jan 6, 2013
Messages
5
Hello everybody,

At work I have a long spreadsheet with the name of products, quantity ordered and quantity delivered. Usually, we can make only one product a day. So, in every cell I have something like this:

GH2345 (3/30)
GH2344 (2/5)
GH2455 (15/17)

What I need to do is to estimate our deadline, extracting the total of products - total of products delivered + today's date.

Ex: For GH2345 30 - 3 = 27 products (or days) + 06/jan = 02/feb.

Any help?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
First off you need to keep your data points separate. It's not impossible to do it this way but it's much cleaner. Is GH2345 a SKU? Have a column labelled "SKU" and have other columns regarding the project...like "completion date", "delivery date", etc. The more data you have the better because you'll be glad later on that you have it. If you want to add more complex features, you'll be set.

If you're trying to estimate a deadline that means you are running under the assumption that a project will take (predictably) X hours/days to complete. If so, you should have another column per item, stating the "hours to completion". This column should be that actual hours it took to complete the project - it is NOT an estimation. By doing this, your estimation macro can gain accuracy the more data points you have.

ex)

SKU | START DATE | END DATE
GH2345 | Nov 25, 2012 | Nov 26, 2012
GH2345 | Nov 28, 2012 | Nov 29, 2012
GH2345 | Nov 29, 2012 | Dec 2, 2012
GH2355 | Nov 25, 2012 | Nov 26, 2012

Based on what you've told me these are the only columns you'll need. Info like "days it took to complete" are abstractions from 'start date' and 'end date'. You should have a button somewhere or put in a right-click context menu item that runs the macro which reports an estimation based on the selected SKU. So for example if you were to right click on a row which has SKU GH2355, the right click menu pops up and shows a menu item called "Estimate a New Job". That runs the macro which does the estimation and in the end, gives you a MsgBox promp telling you what it thinks.

The actual formula(s) for making a proper estimation are much more complex and I'm not going to attempt to look into it for you. You have to consider things like "number of employees working on it", to "an employee was sick that day and the number of employees went down to 2 instead of 3" to more importantly - "how many jobs did we have going at the same time"? Lots to it.
 
Upvote 0
Hello everybody,

At work I have a long spreadsheet with the name of products, quantity ordered and quantity delivered. Usually, we can make only one product a day. So, in every cell I have something like this:

GH2345 (3/30)
GH2344 (2/5)
GH2455 (15/17)

What I need to do is to estimate our deadline, extracting the total of products - total of products delivered + today's date.

Ex: For GH2345 30 - 3 = 27 products (or days) + 06/jan = 02/feb.

Any help?

If GH2345 is in cell A1 and 3/30 is in B1 the following formula in C1 should calculate net workdays using todays date (which will update daily)

=WORKDAY(TODAY(),(RIGHT(B3,LEN(B3)-(FIND("/",B3,1)))-(LEFT(B3,LEN(B3)-(FIND("/",B3,1))))))

As the post above mentions, this does not take into account production capacity fluctuation and workday is Mon-Fri.
If it works :eek: ........ its a lucky first post not skill ;)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,216,086
Messages
6,128,736
Members
449,466
Latest member
Peter Juhnke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top