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

kachaloo

New Member
Joined
Jan 18, 2011
Messages
17
Office Version
2013
Platform
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

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,000
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,000
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
17
Office Version
2013
Platform
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,095,171
Messages
5,442,805
Members
405,198
Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top