Convert decimals (fractions) to hours and minutes

TeeM

Can anyone provide a simple formula to convert decimals/fractions to hours and minutes?

TeeM

to explain further.... e.g. a supplier is filling in a webform to indicate 3hrs and 46mins. They show this as 3.46

However, excel or smartsheet see this as 3.46 hours (which is about 3 hrs and 26 minutes). We need this to be calculating as 3 hours and 46 minutes

Paul Newcome

Take a look through the below thread. You should be able to find something to help you there. I believe we are up to 12 pages though, so you might have to dig a little bit.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

TeeM

@Paul Newcome Hello Paul, I feel like I am right out of my depth now after looking through the various posts. 😄

Could you please look at my sheet and make you an admin user?

Regards

Terry

Paul Newcome

What exactly are you trying to accomplish? What is the end goal for this data conversion?

TeeM

We have a telemarketer working for 5 hrs per week. At the end of each week they send in a webform with the number of actual hours worked and the number of calls made etc.

The totals are shown on a dashboard and one of the primary metrics is the average number of calls per hour since the commencement of engagement. As this is currently only adding up the sum of decimals e.g. 3.46 hrs and not time as in 3 hours and 46 minutes, the data showing on the dashboard is incorrect.

Paul Newcome

In that case you would keep the three and add (0.46 / 60). That would give you the true decimal. You would then get your totals, keep the integer (INT) and multiply the end decimal by 60. This would give you the total time taken but in hh:mm format. There is a solution in that thread that allows you to add up times. You would just need to replace ":" with "." throughout the solution.

TeeM

@Paul Newcome

Many thanks for your help. I will give that a try and see how we get on.