• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stacked bar chart

madocar

Member
Hi everyone,

I would like to ask you if it is possible to show following in a chart:

Product:Hour it takes to create:
Product A80
Product B50
Product C120

One man daily capacity (this might change dynamically):

Daily capacity [Hour]
till 15.6.201915
till 30.6.201910

And this is what I would like to get ... Just like an example, Product A will produce 1,5 day then Product B will produce only 1 day .... etc.
View attachment 60610

Is there any way how to do so?

Thanks a lot for any help

Madocar
 

p45cal

Well-Known Member
Just like an example, Product A will produce 1,5 day then Product B will produce only 1 day .... etc.
Is there any way how to do so?
Yes it can be done. Do you want to account for weekends and holidays too?

ps. I don't understand how Product A needing 80 hours, is complete in one and a half days, unless there are multiple employees involved?
 

madocar

Member
It would be nice if you can help me out.

Yes, you can count with weekends and holidays.

Regarding the example - It was just an example - one and a half day :)
Of course it will be more.

Thanks a lot for any help
 

p45cal

Well-Known Member
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:
60870
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.
 

Attachments

Last edited:

madocar

Member
This is really great!
Thanks a lot p45cal

Perhaps last question, is there any way how to do it without any macro?

Thanks again
 

p45cal

Well-Known Member
I'm sure there is but I wouldn't like to be the one to write it; it'd be a very long formula and nigh on impossible to maintain
 
Top