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

Shanelle
Shanelle ✭✭✭
in Formulas and Functions

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.



image.png


Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Shanelle
    Shanelle ✭✭✭
    Answer ✓

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

    C-O Form 4.JPG
    C-o form 5.JPG


    · Share on FacebookShare on Twitter
«13

Answers

  • Paul Newcome
    Paul 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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul 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(Duration@row) / 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(Duration@row), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT(Duration@row), 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(Duration@row), 24) >= 12, "pm", "am")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Shanelle
    Shanelle ✭✭✭

    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.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul 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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul 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(Duration@row) / 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(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")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Shanelle
    Shanelle ✭✭✭

    Hi Paul,

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


    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you provide examples?

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Shanelle
    Shanelle ✭✭✭


    · Share on FacebookShare on Twitter
  • Shanelle
    Shanelle ✭✭✭

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


    · Share on FacebookShare on Twitter
  • Shanelle
    Shanelle ✭✭✭

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

    Copy of Initial C_O Form.pdf


    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul 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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Shanelle
    Shanelle ✭✭✭

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

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul 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


    image.png


    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Shanelle
    Shanelle ✭✭✭
    Answer ✓

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

    C-O Form 4.JPG
    C-o form 5.JPG


    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!