The attached has a couple of charts in. I'm not as up-to-speed on charts as I should be so there may be a more elegant way to produce them.
The more important aspect of this thread is how to calculate when a Product will be finished from the given number of hours it takes, bearing in mind the differing numbers of hours available in a day according to a calendar and weekends and holidays.
I've written a user-defined function (UDF) to do this and an example of its use in a cell is:
Code:
=ProductFinishedDateTime(J12,I12,$A$2:$B$5,$P$16:$P$19)
If you use the Function arguments dialogue box it looks like:
It's fairly obvious what's what, but
HPDTable is my shorthand for
Hours
Per
Day Table.
This table is supplied by you on the spreadsheet (column A in the attached) but you have to be careful to get the dates right.
I wasn't sure what you meant by '
till 15.6.2019' - whether that included the 15th June as a 15 hour day or not. So to clarify what days have what hours, there's a temporary 'Testing Table' around cells E3:F10 that you can use to test whether the right dates are in column A. The dates have to be in ascending order in the range used for HPDTable. This Testing Table shows you how many hours in any given day the function will use. This table can be deleted - it's not necessary for anything else.
The
Hols (Holidays) argument is optional. The dates in that range don't need to be in any specific order.
The weekends are defined in the UDF as Saturday and Sunday, although this can be changed if you want.
Where you see a time as a result of this formula, it's not a real time; for example you'll maybe see
16/06/2019 12:00 which looks like it's midday, but it's really half a day: if it's a 10 hour day it's 5 hours into that 10 hours, if its a 15 hour day it's 7.5 hours into that 15 hours. It doesn't really matter for the charts.
I'm sure you'll have lots of questions.
