How do you extract numbers out of a cell and create a chart with the data

How do you extract numbers out of a cell that is formatted in a 12-hour clock and paste the numbers into another cell? I need the column "Time Duration" to know the difference between the "Start Time" and "End Time" and then on possible another sheet show a chart of over a period of time.

I was even thinking, I could extract the 7:05 and 7:15 (remove the "AM") into one cell, then in another cell reduce it down to 705 and 715 respectively (remove the " : ") then get the difference.

I don't know where to start, please help!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Smartsheet does not currently support time calculations. I have a few solutions published here in the Community though. I will take a look through my notes and try to find one that could be adapted for you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @FugroKyle Will you also need to incorporate dates?

  • Thank you for your help Paul! I would like to incorporate dates, the purpose behind this is to show my client in a chart style how long it is taking over "X" amount of days to accomplish specific tasks. Then also to drill down to show them on specific days how much time it took. I am apart of the Early Works portion of a construction project, my client wants to be able to forecast how long they can expect it to take to accomplish each specific tasks once the project actually goes into construction operations.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!