Formula Function Help

Options

Current Formula

=IF(ISBLANK([Actual End Time 1]@row), 0, ([Actual End Time 1]@row - [Actual Start Time 1]@row) / 100) + IF(ISBLANK([Actual End Time 2]@row), 0, ([Actual End Time 2]@row - [Actual Start Time 2]@row) / 100)

Results in Actual Duration of 0 if Actual end time 1 is not entered.

Want to update the first part in bold in first formula so Actual Duration is the value in the "Baseline Duration in Hours" Cell if Actual end time is not entered. Otherwise calculate Actual duration by the second part of the formula (in bold). It is not giving me the correct values as you can see in the image below.

=IF(ISBLANK([Actual End Time 1]@row), [Baseline Duration in Hours]@row, ([Actual End Time 1]@row - [Actual Start Time 1]@row) / 100) + IF(ISBLANK([Actual End Time 2]@row), 0, ([Actual End Time 2]@row - [Actual Start Time 2@row ) / 100)

Thank you!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Alicia Delahunty

    What is the length of your working day in your Project Settings? If your Baseline Duration Hours column is set as the Duration in your Project settings, then since the time is set as hours, the formula will present the number as a decimal of the working day.

    For example, my working day is set to 8 hours. This means that a duration of 4 hours is returned as 0.5 in the formula, since it's half of my working day:

    Is it possible that your Working Day is set to 18hrs, which would mean that 4hrs is 0.2222 out of your day?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!