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
-
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:
-
Thanks! I will Review them.
-
Happy to help. 👍️
-
@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!
-
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)
-
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)
-
@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
-
@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?
-
Are you able to provide some screenshots for reference?
-
I can Share the Sheet with you
https://app.smartsheet.com/sheets/jjRW82rF4fP2PRCVVxfGFMq4CVv7f3QrXccJpqv1?view=grid
-
@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:
-
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)
-
@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:
-
@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
-
@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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!