Adding IFError

Anthony Barthelemy

Smartsheet Community

Would be IF Error go at the beginning of this Formula so I do not get Invalid Value when the Cells are empty

=INT((((IF(LEFT(Endtime@row, FIND(":", Endtime@row) - 1) = "12", IF(OR(FIND("a", Endtime@row) > 0, FIND("p", Endtime@row) > 0), 0, 12), VALUE(LEFT(Endtime@row, FIND(":", Endtime@row) - 1))) + IF(FIND("p", Endtime@row) > 0, 12)) * 60 + VALUE(MID(Endtime@row, FIND(":", Endtime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(Endtime@row, FIND(":", Endtime@row) - 1) = "12", IF(OR(FIND("a", Endtime@row) > 0, FIND("p", Endtime@row) > 0), 0, 12), VALUE(LEFT(Endtime@row, FIND(":", Endtime@row) - 1))) + IF(FIND("p", Endtime@row) > 0, 12)) * 60 + VALUE(MID(Endtime@row, FIND(":", Endtime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) - INT((((IF(LEFT(Endtime@row, FIND(":", Endtime@row) - 1) = "12", IF(OR(FIND("a", Endtime@row) > 0, FIND("p", Endtime@row) > 0), 0, 12), VALUE(LEFT(Endtime@row, FIND(":", Endtime@row) - 1))) + IF(FIND("p", Endtime@row) > 0, 12)) * 60 + VALUE(MID(Endtime@row, FIND(":", Endtime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(Endtime@row, FIND(":", Endtime@row) - 1) = "12", IF(OR(FIND("a", Endtime@row) > 0, FIND("p", Endtime@row) > 0), 0, 12), VALUE(LEFT(Endtime@row, FIND(":", Endtime@row) - 1))) + IF(FIND("p", Endtime@row) > 0, 12)) * 60 + VALUE(MID(Endtime@row, FIND(":", Endtime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) - INT((((IF(LEFT(Endtime@row, FIND(":", Endtime@row) - 1) = "12", IF(OR(FIND("a", Endtime@row) > 0, FIND("p", Endtime@row) > 0), 0, 12), VALUE(LEFT(Endtime@row, FIND(":", Endtime@row) - 1))) + IF(FIND("p", Endtime@row) > 0, 12)) * 60 + VALUE(MID(Endtime@row, FIND(":", Endtime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) / 60) * 60)

Paul Newcome

Yes. It would look something like this...

=IFERROR(original_formula, "")

However, there are much more efficient ways for calculating time. Take a look at this thread:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Anthony Barthelemy

Thanks! I will Review them.

Paul Newcome

Happy to help. 👍️

Anthony Barthelemy

@Paul Newcome I Use this Formula but I am Receiving an Uparseable. I double checked 4 time to make sure entered it exactly, but still same error. I also Change that Date Format to match yours, but same Result.

Here is my Formula:

=((VALUE(LEFT([End Time]@row, FIND(".", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([Finish Date (Actual)]@row - [Start Date (Actual)]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(".", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)

Column Format

Start Date (Actual) & Finish Date (Actual) : Tue 08/31/2021

Start Time & End Time: HH:MM

Thanks in Advance!

Paul Newcome

Does this work?

=((VALUE(LEFT([End Time]@row, FIND(".", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2))) / 60) + (([Finish Date (Actual)]@row - [Start Date (Actual)]@row) * 24) - ((VALUE(LEFT([Start Time]@row, FIND(".", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2))) / 60)

Anthony Barthelemy

No, I Received Invalid Value.

However, I Found the Error. In the FIND I had a period instead of a colon.

Thanks I Appreciate your Help.

Updated Formula:

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2))) / 60) + (([Finish Date (Actual)]@row - [Start Date (Actual)]@row) * 24) - ((VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2))) / 60)

Anthony Barthelemy

@Paul Newcome It Seems I have ran into another Problem with the Difference Column. I can not decrease the decimals

Current Formula:

=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60

Results: 13:39.99998

Expected Results: 13:39

Anthony Barthelemy

@Paul Newcome My Calculation Must be off.

Start time at 16:30

Finish time at 17:30

My Sum should 1, right?

Instead I am getting 14

Your thoughts?

Paul Newcome

Are you able to provide some screenshots for reference?

Anthony Barthelemy

I can Share the Sheet with you

https://app.smartsheet.com/sheets/jjRW82rF4fP2PRCVVxfGFMq4CVv7f3QrXccJpqv1?view=grid

Anthony Barthelemy

@Paul Newcome Screen shots Below. Although I think Providing you Access to the Sheet would have been quicker

Screen shot 1:

Screen Shot 2:

Screen Shot 3:

Paul Newcome

For the decimal issue, we need to incorporate a ROUNDDOWN function.

=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + ROUNDDOWN((SUM@row - INT(SUM@row)) * 60)

For the incorrect calculation try this in the SUM column instead:

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2))) / 60) + (([Finish Date (Actual)]@row - [Start Date (Actual)]@row - 1) * 24) - ((VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2))) / 60)

Anthony Barthelemy

@Paul Newcome (Row 2) I receive Negative Numbers, the Hours Calculation is still incorrect, and the Formula has a Dash after the Minutes. Screen shot Below:

Anthony Barthelemy

@Paul Newcome Can you Allow me to Copy your Sheets? That way I can study your exact formula in your sheets and see where I have errors.

Thanks

Anthony Barthelemy

@Paul Newcome I found a work around for the hours. However I think I have 1 too many parenthesis, but can not find where in both formulas.

Can you Take a Look?

=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2))) / 60) + (([Finish Date (Actual)]@row - [Start Date (Actual)]@row) * 24) - ((VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2))) / 60)