Creating an end time based on an amount of time and a start time.

Keenan Kibrick
edited 02/12/21 in Formulas and Functions

I am trying to create a formula so that if I start something at 7:00 AM and then say it will be 30 minutes long, an end time column created at the would display 7:30 AM.

I looked through all the time formulas and could not get any to work properly. I admit that might be my lack of formula knowledge. I know already that Smartsheet has problems with time, but if anyone can help me make a formula for this I would greatly appreciate it.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/14/21

    Hi @Keenan Kibrick

    Hope you are fine, i created a sample sheet for your question, and the following is the formula to calculate end time using the created time ( System column created ) and duration.

    1- Hours Column =VALUE(MID(Created@row, 12, 2)) convert to column formula

    2- Minutes Column =VALUE((MID(Created@row, 15, 2))) convert to column formula

    3- Total Minute =IF((Duration@row + Minutes@row) > 60, (Duration@row + Minutes@row) - 60, (Duration@row + Minutes@row)) convert to column formula

    4- Total Hours =IF((Duration@row + Minutes@row) > 60, ROUND((Duration@row + Minutes@row) / 60)) + VALUE(Hours@row) convert to column formula

    5- Period =MID(Created@row, 18, 5) convert to column formula

    6- End Time =(IF([Total Hours]@row < 10, "0" + [Total Hours]@row, [Total Hours]@row) + ":" + IF([Total Minute]@row < 10, ("0" + [Total Minute]@row), [Total Minute]@row + " " + Period@row)) convert to column formula

    the following screenshot show the sample sheet


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • I am doing something similar but far less complex b/c the time is a manual entry in Column A (let's say 7pm). I would like to use a formula to show 3 hours prior to the time in Column A (show 4pm in Column B). How would I do that? Would I use the same logic as above and create helper columns to extract hour, subtract by 3, and then show the final?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!