How do I convert Hours in time to a number that can be summarized?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Date Time
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 😀👍
