# Convert Time into number for Payroll calculations in PowerQuery [Office 2013]

#### kachaloo

##### New Member
Hey guys.
I am doing my first Power Query project using and have successfully transformed data are I wanted that I could not do in PowerPivot. However I need to convert the hours worked by each employees into Number so that I can take that number and use it in the payroll software to calculate wages.

I am using the formula =INT(A1)*24+HOUR(A1)+ROUND(MINUTE(A1)/60,2) to convert it numbers however I am struggling to understand how can I use it in PowerQuery by adding a new column as.

Also while on topic I am also looking for way to clear data by second last of the month and adding last month final week from Saturday till end of the month. i.e whats left over from previuos month after 2nd last friday of the month. and tag it say MAR20 from 22/02/2020 to 20/03/2020. At the moment I have it manually entered as Feb20 and March 20..

Thanks

#### Attachments

• hours worked.JPG
49.3 KB · Views: 5

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### sandy666

##### Banned - Rules violations
Table.AddColumn(#"Previous step", "Hour", each Time.Hour([aa]), Int64.Type)
Table.AddColumn(#"Previous step", "Minute", each Time.Minute([aa]), Int64.Type)
Table.TransformColumns(#"Previous step", {{"Minute", each _ / 60, type number}})

Last edited:

#### sandy666

##### Banned - Rules violations
or just add custom [Payable Hours] column: `Time.Hour([Hours Worked])+Time.Minute([Hours Worked])/60`

Last edited:

#### kachaloo

##### New Member
wow thanks for a quick reply.
Yes it perfect. Thank you.
Time.Hour([Hours Worked])+Time.Minute([Hours Worked])/60

#### sandy666

##### Banned - Rules violations
You are welcome
Thanks for the feedback

Replies
3
Views
221
Replies
1
Views
98
Replies
8
Views
254
Replies
1
Views
67
Replies
4
Views
275

1,127,504
Messages
5,625,191
Members
416,079
Latest member
lizziebee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back