Planing a load of production

Krzysztof Żebruń

Hello

 

I'm trying to make sheet that I will be able to put orders, amount of planed hours on task and devide for different type of teams, employees. 

 

I have stuck on one logic function. At the moment, when not all informations are typed in, function below returns blank cell.

 

=IF(ISERROR(PRORATE($[Total hours]32, $Start32, $Finish32, DATE(2017, [1]$2, [1]$4), DATE(2017, [1]$3, [1]$5))), "", PRORATE($[Total hours]32, $Start32, $Finish32, DATE(2017, [1]$2, [1]$4), DATE(2017, [1]$3, [1]$5)))

 

I would like to add to it also logic function, that will return blank cell if result of function is equal 0 (zero)

 

Do You have any idea?

 

Example of this sheet is available below.

 

https://app.smartsheet.com/b/publish?EQBCT=0d91ca8bc3bb4ae096bce2b0d4c3b280

 

 

J. Craig Williams

That's quite a sheet you have there.

 

First, try changing your IF(ISERROR()) to IFERROR()

 

around your the whole function, add your if = 0

 

so

 

=IF (   IFERROR ( PRORATE(), "") = 0, "", PRORATE() )

I really don't like repeating the PRORATE() calc, but your sheet is already complicated enough without adding more columns. Because you have PRORATE throwing errors, you get into a vicious circle of trying to catch it.

 

Hope that helps.

 

Craig

Krzysztof Żebruń

Thank You a lot.

 

This have solved my problem with displaying 0. 

 

I have another question.

 

In Prorate function i need to use range of dates for calculation. In my case range is a calendar week.

 

Easiest way for me was to create by hand 4 rows that contain a month and day at beginning of a week and end of a week.

 

Row 2 contain month in first day of a week

Row 3 contain month in last day of a week

Row 4 contain day in first day of a week

Row 5 contain day in last day of a week

 

DATE(2017, [1]$2, [1]$4), DATE(2017, [1]$3, [1]$5)

 

Is there more simple solution for finding this range?

 

 

J. Craig Williams

Do you need all of those columns for other reasons?

 

If so, the solution you have, while complicated on the surface, gets the job done.

 

Craig

Krzysztof Żebruń

Column 1 -52 represents a week number. 

 

I did not find any other way to both have gant chart and table with amount of hours based on task --> team.

 

Basically I need it to get some forecast of load in workshop, to plan when I can take another order etc.

 

If there is no better way I will work with solution that i have. 

 

Thank You for help