Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

HH:MM calculation in Smartsheet

I need to do a time motion study of a process, so will use HH:MM format to get total time in minutes, I am finding it difficult in smartsheet, could you please help me how can I easily subtract Start time and finish time to get total time…

I searched on smartsheet community, on this link : https://community.smartsheet.com/discussion/44746/time-formats-number-formats-rethinking-the-ux-and-function

I got one formula : =VALUE([Start Hr]2@row) + (VALUE([Start Min]2@row) / 60)

But I am getting #unparseble error.

Ex.



Best Answers

  • Community Champion
    edited 08/11/20 Answer ✓

    =(ROUNDDOWN([End Time]@row, 0) - ROUNDDOWN([Start Time]@row, 0)) * 60 + ([End Time]@row - ROUNDDOWN([End Time]@row, 0) - ([Start Time]@row - ROUNDDOWN([Start Time]@row, 0))) * 100


    Give this one a try to get your total minutes easier with less helper columns. Give me a bit and i'll write out the other formula to convert this into hours:minutes

Answers

  • Community Champion
    edited 08/11/20 Answer ✓

    =(ROUNDDOWN([End Time]@row, 0) - ROUNDDOWN([Start Time]@row, 0)) * 60 + ([End Time]@row - ROUNDDOWN([End Time]@row, 0) - ([Start Time]@row - ROUNDDOWN([Start Time]@row, 0))) * 100


    Give this one a try to get your total minutes easier with less helper columns. Give me a bit and i'll write out the other formula to convert this into hours:minutes

  • Community Champion

    =ROUNDDOWN([total time]@row / 60) + ":" + MOD([total time]@row, 60)

    This will convert it into the correct format. you can change the column to whichever you like

  • ✭✭✭✭✭

    Hi Rahul

    In your formula:

    =VALUE([Start Hr]2@row) + (VALUE([Start Min]2@row) / 60)

    It's either VALUE([Start Hr]2) + (VALUE([Start Min]2) / 60)

    or VALUE([Start Hr]@row) + (VALUE([Start Min]@row) / 60)

    You can't have [Start Hr]2@row :)

  • ✭✭

    Hello, I made a timesheet and wage calculation table.

    The data is sent by employees using a form.


    I have a problem with the time calculation.🙄


    Here is the sheet.

    Below is what I don't understand and I don't know where the error is.

    I would be grateful if someone could check it. Thanks!

  • ✭✭

    Sorry, but I messed up the example in my previous post. It shows the end time incorrectly. Below is the corrected one.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a feature column, a task (multiple tasks per feature) column, a task date column and a feature date column. The task dates can be of three different types. They can be all dates. They can be da…
    User: "charish"
    Answered ✓
    30
    5
  • I am trying to use a formula with CHAR(10) between each missing asset item to build a nice list to use in a record search return automation. I am using one row for each employee with multiple uniform …
    User: "Michelle Rogers"
    Answered ✓
    16
    4
  • Hello, Everyone. I have a commission sheet with many columns, and I have a 3 part formula to calculate commission based on 3 different % depending on which month of the contract the sales team is in. …
    User: "Paul.Woodward"
    Answered ✓
    21
    3