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

kachaloo

New Member
Joined
Jan 18, 2011
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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
    hours worked.JPG
    49.3 KB · Views: 5

Some videos you may like

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
Joined
Oct 24, 2015
Messages
7,499
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}})
Table.AddColumn(#"Previous step", "Addition", each [Hour] + [Minute], type number)
conv.png
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
or just add custom [Payable Hours] column: Time.Hour([Hours Worked])+Time.Minute([Hours Worked])/60
 
Last edited:

kachaloo

New Member
Joined
Jan 18, 2011
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
wow thanks for a quick reply.
Yes it perfect. Thank you.
Time.Hour([Hours Worked])+Time.Minute([Hours Worked])/60
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top