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'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
    User: "Allan Z"
    Answered ✓
    9
    2
  • Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
    User: "stratman"
    Answered ✓
    15
    2
  • I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …
    User: "m_anderson"
    Answered ✓
    13
    2