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




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.






  • J. Craig Williams
    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




    =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.



  • 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
    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.



  • Krzysztof Żebruń
    edited 02/02/17

    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.