How do I convert Hours in time to a number that can be summarized?

[SAConsultant]

Hello,

I have a dataset of payroll information from Quickbooks containing hours worked by day and individual.  I need to be able to summarize hours worked by individual, but am having trouble finding the appropriate method to convert the hours into a numerical format that can be transformed with a summarize tool. Can someone help me determine how to convert the hours into a format that can be summarized?

Attached excel contains a cleansed subset of the base input. Columns A-C reflect the raw data, and column D reflects my attempt to the convert the hours from column C(which has a data type of "Time" in designer) to numbers (using a ToNumber function)

As you can see, anytime an individual has hours that are not whole (i.e. rows 3,4, and 6), the conversion isn't accurate. Column C currently reads as Hours:minutes:seconds.

[markcurry · Accepted solution]

Hi @SAConsultant   I think the following formula will do the trick for you, to give you the time in minutes (ignoring any seconds)....

(ToNumber(Substring([Hours],0,2)) * 60) + ToNumber(Substring([Hours],3,2))

[atcodedog05]

Hi @SAConsultant 

Is this what you are looking for

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍