is there a formula i can use for the below

Options

is there a formula i can use i have a form which i use for staff hours when they input start time an finish time

i want it to automatically add it to total time


Answers

  • Lachlan Stead
    Lachlan Stead ✭✭✭✭✭✭
    Options

    Hello Aaron,

    Be keen to know if there is a better solution but we had to add two hidden columns also, so in the end it looked like the below:

    The start and end time columns are drop downs, structured like the below:

    7:00:00 AM-7
    7:15:00 AM-7.25
    7:30:00 AM-7.5
    7:45:00 AM-7.75
    8:00:00 AM-8
    8:15:00 AM-8.25 etc. etc.


    Then the numeric start time is a column formula: =VALUE(RIGHT([Start Time]@row, LEN([Start Time]@row) - FIND("-", [Start Time]@row)))


    Numeric finish time: =VALUE(RIGHT([Finish Time]@row, LEN([Finish Time]@row) - FIND("-", [Finish Time]@row)))


    Total hours: =[Numeric Finish Time (Hidden)]@row - [Numeric Start Time (Hidden)]@row

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!