Forum Discussion
Combining multiple columns to add time together
ok, create a new column. and add this code
each Duration.FromText([Rate]) / #duration(0, 0, 1, 0))This converts the hours into minutes. Delete the Rate column. Highlight the date column, go to the Ribbion > Home > Group by Operation is Sum and choose the new minutes column to do the sum.
This will group all the minutes in to one day. You can then divide by 60 to get the hours worked.
Thanks
Joe
JoeBarry If we dont look at it as different rates then yes result would be 09:00
Since 01:00 is overtime and still needs to be included in total time for the day.
ok, create a new column. and add this code
each Duration.FromText([Rate]) / #duration(0, 0, 1, 0))
This converts the hours into minutes. Delete the Rate column. Highlight the date column, go to the Ribbion > Home > Group by Operation is Sum and choose the new minutes column to do the sum.
This will group all the minutes in to one day. You can then divide by 60 to get the hours worked.
Thanks
Joe
sorry, i copied the code from the formula bar, please remove the each part. Also please filter out the blank values in the rate column beforehand
JoeBarry I did Group by and Sum and it worked, but it removed all other columns excelp date and new column that resulted from group by.
Is there a way to bring other columns back while keeping the column that resulted from group by?
So you still need the breakdown of where the hours went?
You could before unpivoting, make duplicates of the Rate columns and then make sure they are highlighted along with the date when grouping.
JoeBarry I might try that, but what I am more need is other information. There were other columns like employee id, clock in and out columns and unique ID that is for everyhing that happens on that Date.
Do I just select those columns when I do group by so that they would not dissapear?