Error calculating time

DawnC

I believe I have read through every thread on this topic and it has been extremely helpful. However I'm getting a weird error that hopefully someone can help me with. I'm getting a completion time of "10:05 04pm" in row 3, I'm sure this is due to the amount of time for the task - I just don't know how to write this so this results in 10:05pm w/o the "04". Any help would be greatly appreciated.

In my completion time column I have the following formula:

=MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) + IF(MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 < 10, "0") + ((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 + IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")

Paul Newcome

It looks like it is actually 05.04 (with the "point").

You are going to need to adjust the portion of your formula that outputs the minutes to remove the decimal portion. You are most likely going to need to use the ROUND function.

I also suggest spreading this out across multiple columns so that future troubleshooting is more manageable.

Here is a link to a number of time based solutions that may also help:

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

DawnC

I made some changes found in the Formulas for Creating Time solution. Thank you for your help with this. I am still having trouble with the end date. If the task pushes the end date into the next day I'm not sure how to create the formula to account for that.

https://app.smartsheet.com/sheets/5x3Vg8h84J8FG5pr5f5j8VCGrvM87WGw9c45wJM1

The highlighted column "Completion Date" is where I'm stumped.

Paul Newcome

I do not have access to the sheet.

Please take a look at the above linked thread. I do know there is a solution in there that adds time to a date and can account for the time pushing into a different date.