#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Planing a load of production

Options
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

That's quite a sheet you have there.

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

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

• Options

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?

• ✭✭✭✭✭✭
Options

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

• edited 02/02/17
Options

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

This discussion has been closed.