Formula to calculate End Date and Time based on Start Date, Time and Task duration

04/24/20
Accepted

Hello,

I'm looking for a formula to calculate the End Date and Time (MM/DD/YYYY)( HH:MM AM/PM) based on the Start Date and Start Time entered on a form using the Task standard routing time.




Best Answers

  • Accepted Answer

    Could you share the formulas in your sheet? I'm still getting errors.


Previous1

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I have some ideas, but I will have to get back to you. Calculations involving time in Smartsheet can get a little tricky, so I want to build a mockup to make sure I get it right.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. You are going to want to add a column that for these formulas I called "Duration". In this column, you will use:

    =(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


    In the Completion Date column:

    =[Actual Clean Start Date]@row + INT(INT([email protected]) / 24)


    In the Completion Time column:

    =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([email protected]), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT([email protected]), 24) >= 12, "pm", "am")), 12)) + ":" + ((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([email protected]), 24) >= 12, "pm", "am")

  • Hi Paul,

    Thank you for answering. I am still pretty new to Smartsheet and after reading numerous posts about the time function, I knew this was going to be troublesome. The time for the 1st entry worked but I still need the date, especially if the clean time goes over 24 hours. The 2nd entry gave me 6:0pm, which also does not include the date. Maybe I placed the duration column in the wrong place. Ideally, we would enter in the Clean start time, and the Standard Clean Routing (Hrs) and the calculation would give us the expected Completion date and time in the same column if possible but if not that is fine too.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Shanelle The date solution is in my above post wedged between the two larger formulas. For 6:0pm... I thought I had taken care of that (minutes under 10). I'll dig in and get back to you with that fix shortly.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Shanelle Here is your fix for minutes under 10... I also just saw where you wanted date and time in the same column. You can combine the date and time formulas into one with a space in between like so:

    =[Actual Clean Start Date]@row + INT(INT([email protected]) / 24) + " " + 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([email protected]), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT([email protected]), 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([email protected]), 24) >= 12, "pm", "am")

  • Hi Paul,

    The formula works for the most part but I'm still getting a couple of weird numbers.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you provide examples?

  • I attached screen shots in my post but I don't see them when I actually post the comment.


  • I'm trying this again. Not sure why nothing is uploading.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I don't see any screenshots either. I just saw this morning where someone else wasn't able to post screenshots. It ended up being a browser thing. Try switching browsers and see if that works.


    Additionally... How do you feel about extra "helper" columns? They can be hidden after setup to keep the sheet looking clean, and it would allow us to break the monster formula down into sections. Breaking it down into sections has quite a few different benefits to include easier troubleshooting and shorter formulas overall since we can use cell references in place of repeating larger sections.

  • I'm fine with helper columns, as long as it doesn't require a lot of additional manual entry.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. The helper columns would all contain formulas. You would only have to enter them once, dragfill them down to cover all of the rows already entered, and then everything will be automated and autofill should grab them when new rows are added.


    So I replicated your data from your PDF. That is really odd behavior for sure, and I am not certain why it is acting that way. I was able to get the desired results by leaving the date and the time in separate columns then using cell references to combine them like so:

    =[Completion Date]@row + " " + [Completion Time]@row



Sign In or Register to comment.