I am working on a project truck movement, I want to extract time duration in hours or minutes.

Is there any formula to extract the duration time? I tried by enable the dependency but it calculate duration in days. If it is possible to extract time in Hours/minutes please help me out. Thanks in advance .

Best Answer

  • Navneetkaur
    Answer ✓

    Thanks #Paul Newcome, I used your duration time formula and got the duration in hours and minutes

    =(VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row, 1) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row, 1) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))


    Many Thanks

    Navneet Kaur

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi Navneetkaur,

    In which column you are calculating the Duration in your Project Sheet? I would suggest creating a Separate column and adding hours and minutes and then calculating the time with the help of Formula.


    Thank You

    Kaveri Vipat, Smartsheet Engineer, Ignatiuz Software

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Hi Kaveri, thanks for the response, I tried this by enable the dependency but it gives me duration only in days. please can you suggest any time formula with the help of extract the duration in minutes/hours. Definitely, I will create e separate column for duration but please suggest me the time formula so i can extract exact duration between Movement time and completed time.

    Thank you

    Navneet Kaur

  • Navneetkaur
    Answer ✓

    Thanks #Paul Newcome, I used your duration time formula and got the duration in hours and minutes

    =(VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row, 1) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row, 1) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))


    Many Thanks

    Navneet Kaur

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!