Adding IFError

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)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!