Hours and Minutes calculation

Hi,

I am attempting to automate a calculation that will support the conversion of hours to minutes. I have a form the employees enter the amount of time spent with one initiative.

The formula I attempted returns #INVALID VALUE. The value 90 is a manual entry.

Last formula: =TIME([Time Allocation]@row) /60

Time Allocation: Dropdown List (Values):

15m
30m
45m
1hr
1.5hr
2hr
3hr
4hr
5hr

Ultimately, I will create a report that calculates Dawn's total hours per month.

Example: Dawn calculated 240 minutes during the month of July.

Thank you!

Best Answer

  • Mark.poole
    Mark.poole Community Champion
    edited 02/04/25 Answer ✓

    @Adarian Searcy

    Try this.

    =IFERROR(IF(CONTAINS("hr", [Time Allocation]@row), VALUE(SUBSTITUTE([Time Allocation]@row, "hr", "")) * 60, VALUE(SUBSTITUTE([Time Allocation]@row, "m", ""))), "")

    This formula searches for an hr in the time allocation column then returns the value multiplied by 60, If hr is not there it will return the value of min. If neither is in the column it returns a blank.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole Community Champion
    edited 02/04/25 Answer ✓

    @Adarian Searcy

    Try this.

    =IFERROR(IF(CONTAINS("hr", [Time Allocation]@row), VALUE(SUBSTITUTE([Time Allocation]@row, "hr", "")) * 60, VALUE(SUBSTITUTE([Time Allocation]@row, "m", ""))), "")

    This formula searches for an hr in the time allocation column then returns the value multiplied by 60, If hr is not there it will return the value of min. If neither is in the column it returns a blank.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Adarian Searcy
    Adarian Searcy ✭✭✭✭

    Thank you, @Mark.poole this function worked as intended.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!