Convert time duration into a value I can multiply

[mduntley]

I am pasting in a chart from my time clock system, but for the duration, if its more then 24 hours, excel will convert it to a date. I am looking a way to calculate my paycheck from the table I inserted.

White text is what pasted from my time clock, but I want something like what is in yellow so I can multiply my wage (in this example $8/hr)

Book1
	A	B	C	D
1	Pay Code	Amount	Calculate	$8.00 per HR
2	2ND SHIFT DIFF	4:00	4	32
3	PTO SCHEDULED	48:00:00	48	384
4	REGULAR	4:30	4.5	36
5	REGULAR	24:00:00	24	192
6	REGULAR	32:00:00	32	256
7	WEEKEND 2ND SHIFT DIFF	12:00	12	96
8	WEEKEND DIFF	12:45	12.75	102
Sheet2

This is my starting table for Power Query

Book1
	A	B
1	Pay Code	Amount
2	2ND SHIFT DIFF	4:00
3	PTO SCHEDULED	48:00:00
4	REGULAR	4:30
5	REGULAR	24:00:00
6	REGULAR	32:00:00
7	WEEKEND 2ND SHIFT DIFF	12:00
8	WEEKEND DIFF	12:45
Sheet2

 

[arthurbr]

D2 would be

=B2*24*$D$1

and pull down ( just enter 8 in D1 to make it simpler) - Format as number - Col C is not necessary

 

[GraH]

Can be done like this

Book1
	C	D
2	Pay Code	Amount
3	2ND SHIFT DIFF	4:00
4	PTO SCHEDULED	48:00:00
5	REGULAR	4:30
6	REGULAR	24:00:00
7	REGULAR	32:00:00
8	WEEKEND 2ND SHIFT DIFF	12:00
9	WEEKEND DIFF	12:45
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Set_types = Table.TransformColumnTypes(Source,{{"Pay Code", type text}, {"Amount", type duration}}),
    Calc_HR = Table.AddColumn(Set_types, "8$ per HR", each Duration.TotalSeconds([Amount])/3600*8)
in
    Calc_HR

Book1
	G	H	I
2	Pay Code	Amount	8$ per HR
3	2ND SHIFT DIFF	0.04:00:00	32
4	PTO SCHEDULED	2.00:00:00	384
5	REGULAR	0.04:30:00	36
6	REGULAR	1.00:00:00	192
7	REGULAR	1.08:00:00	256
8	WEEKEND 2ND SHIFT DIFF	0.12:00:00	96
9	WEEKEND DIFF	0.12:45:00	102
Sheet2