Multiply and convert minutes to hours

[Panoos64]

Hi all, kindly require to provide me support so that to create a VBA code which should convert the minutes decimals in col. "L" to hours multiplying by 0.60. On my first below extract i appear the original data and on second i appear the expected result.

e.g. 01-Aug-2018 9.67 should be 9.40 (67*0.60=40.20). we get the first decimals (40) and should turn the number 9.67 to 9.40.

Thanks in advance

Original data

​

<tbody>
[TD="class: xl64, align: center"][/TD]
[TD="class: xl74, width: 72, align: center"]A[/TD]
[TD="class: xl74, width: 170, align: center"]B[/TD]
[TD="class: xl74, width: 88, align: center"]C[/TD]
[TD="class: xl74, width: 66, align: center"]D[/TD]
[TD="class: xl74, width: 71, align: center"]E[/TD]
[TD="class: xl74, width: 60, align: center"]F[/TD]
[TD="class: xl74, width: 68, align: center"]G[/TD]
[TD="class: xl77, width: 64, align: center"]L[/TD]

[TD="align: center"]1[/TD]
[TD="class: xl65, align: center"]CODE[/TD]
[TD="class: xl65, align: center"]Name[/TD]
[TD="class: xl65, align: center"]Date[/TD]
[TD="class: xl71, width: 66, align: center"]TIME ON DUTY[/TD]
[TD="class: xl70, width: 71, align: center"]TIME OFF DUTY[/TD]
[TD="class: xl65, align: center"]IN[/TD]
[TD="class: xl65, align: center"]OUT[/TD]
[TD="class: xl65, align: center"]W. HRS[/TD]

[TD="align: center"]2[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl72, align: center"]EMPLOYEE 1[/TD]
[TD="class: xl69, align: center"]01-Aug-18[/TD]
[TD="class: xl68, align: center"]08:00[/TD]
[TD="class: xl68, align: center"]17:00[/TD]
[TD="class: xl76, align: center"]7:30[/TD]
[TD="class: xl76, align: center"]17:10[/TD]
[TD="class: xl66, align: center"]9.67[/TD]

[TD="align: center"]3[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl72, align: center"]EMPLOYEE 1[/TD]
[TD="class: xl69, align: center"]02-Aug-18[/TD]
[TD="class: xl68, align: center"]08:00[/TD]
[TD="class: xl68, align: center"]17:00[/TD]
[TD="class: xl76, align: center"]7:45[/TD]
[TD="class: xl75, align: center"]16:30[/TD]
[TD="class: xl66, align: center"]8.75[/TD]

[TD="align: center"]4[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl72, align: center"]EMPLOYEE 1[/TD]
[TD="class: xl69, align: center"]03-Aug-18[/TD]
[TD="class: xl68, align: center"]08:00[/TD]
[TD="class: xl68, align: center"]17:00[/TD]
[TD="class: xl76, align: center"]8:14[/TD]
[TD="class: xl76, align: center"]17:20[/TD]
[TD="class: xl66, align: center"]9.10[/TD]

[TD="align: center"]5[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl72, align: center"]EMPLOYEE 1[/TD]
[TD="class: xl69, align: center"]04-Aug-18[/TD]
[TD="class: xl68, align: center"]08:00[/TD]
[TD="class: xl68, align: center"]17:00[/TD]
[TD="class: xl76, align: center"]7:45[/TD]
[TD="class: xl75, align: center"]16:14[/TD]
[TD="class: xl66, align: center"]8.48[/TD]

</tbody>

Expected result

<tbody>
[TD="class: xl66, align: center"][/TD]
[TD="class: xl76, width: 72, align: center"]A[/TD]
[TD="class: xl76, width: 170, align: center"]B[/TD]
[TD="class: xl76, width: 88, align: center"]C[/TD]
[TD="class: xl76, width: 66, align: center"]D[/TD]
[TD="class: xl76, width: 71, align: center"]E[/TD]
[TD="class: xl76, width: 60, align: center"]F[/TD]
[TD="class: xl76, width: 68, align: center"]G[/TD]
[TD="class: xl79, width: 64, align: center"]L[/TD]

[TD="align: center"]1[/TD]
[TD="class: xl67, align: center"]CODE[/TD]
[TD="class: xl67, align: center"]Name[/TD]
[TD="class: xl67, align: center"]Date[/TD]
[TD="class: xl73, width: 66, align: center"]TIME ON DUTY[/TD]
[TD="class: xl72, width: 71, align: center"]TIME OFF DUTY[/TD]
[TD="class: xl67, align: center"]IN[/TD]
[TD="class: xl67, align: center"]OUT[/TD]
[TD="class: xl67, align: center"]W. HRS[/TD]

[TD="align: center"]2[/TD]
[TD="class: xl69, align: center"]1[/TD]
[TD="class: xl74, align: center"]EMPLOYEE 1[/TD]
[TD="class: xl71, align: center"]01-Aug-18[/TD]
[TD="class: xl70, align: center"]08:00[/TD]
[TD="class: xl70, align: center"]17:00[/TD]
[TD="class: xl78, align: center"]7:30[/TD]
[TD="class: xl78, align: center"]17:10[/TD]
[TD="class: xl68, align: center"]9.40[/TD]

[TD="align: center"]3[/TD]
[TD="class: xl69, align: center"]1[/TD]
[TD="class: xl74, align: center"]EMPLOYEE 1 [/TD]
[TD="class: xl71, align: center"]02-Aug-18[/TD]
[TD="class: xl70, align: center"]08:00[/TD]
[TD="class: xl70, align: center"]17:00[/TD]
[TD="class: xl78, align: center"]7:45[/TD]
[TD="class: xl77, align: center"]16:30[/TD]
[TD="class: xl68, align: center"]8.45[/TD]

[TD="align: center"]4[/TD]
[TD="class: xl69, align: center"]1[/TD]
[TD="class: xl74, align: center"]EMPLOYEE 1[/TD]
[TD="class: xl71, align: center"]03-Aug-18[/TD]
[TD="class: xl70, align: center"]08:00[/TD]
[TD="class: xl70, align: center"]17:00[/TD]
[TD="class: xl78, align: center"]8:14[/TD]
[TD="class: xl78, align: center"]17:20[/TD]
[TD="class: xl68, align: center"]9.06[/TD]

[TD="align: center"]5[/TD]
[TD="class: xl69, align: center"]1[/TD]
[TD="class: xl74, align: center"]EMPLOYEE 1[/TD]
[TD="class: xl71, align: center"]04-Aug-18[/TD]
[TD="class: xl70, align: center"]08:00[/TD]
[TD="class: xl70, align: center"]17:00[/TD]
[TD="class: xl78, align: center"]7:45[/TD]
[TD="class: xl77, align: center"]16:14[/TD]
[TD="class: xl68, align: center"]8.29[/TD]

</tbody>

 

[steve the fish]

Can i ask why? Is it just for a visual? If so why not divide the number by 24 and format to time. If you are doing any calculations with that converted number they will be wrong (sum for example).

 

[Panoos64]

Hi steve, i already done it. Is a formula by which like, =SUM(G2-F2)*24, and it appears the result 9.67. So i have to convert the minutes to hours proportion but using VBA. Thanks for your support

 

[steve the fish]

Im still not understanding why? If you just use =G2-F2 you can then format to time and see what you want.

 

[Panoos64]

Hi, lets check 2/8/18 =G2-F2=8.85. My query is that, there is NO such hour. So we should convert 0.85 against 1 hour. (method of three equation) The formula should be 85 * 0.60 = 0.51 against hour. The actual working hours for that date is 8.51 and not 8.85. Note that 0.10 difference is due to rounding and do not taking into consideration.

 

[steve the fish]

Ok so we are talking about =G3-F2 ? What im saying is do not then multiply that by 24. Instead just format that cell to time. Right click, format cells, time. You will then see what you ask for. If you really want to get L3 from 8.75 to 8.45 you can do this:

=INT(L3)+MOD(L3,1)*0.6

 

[Panoos64]

Hi, Ok thank you. I used your first suggestion by format, cells, time and it works perfect but now i cannot prepare the "SUM" for each employee end of month. i couldn't understand your formula. Thanks once again for your support.

 

[steve the fish]

You can sum time format the sum to custom format [h]:mm

 

[mikerickson]

It would be easier if all the time entries were in excel serial time rather than decimal hours.

The formula in L2 would be =G2-F2. The cell could be formatted to one of the date formats.

When downstream calculations need hours, that is the point to convert from Excel Serial Time to decimal hours.

 

[Panoos64]

hi mike, i agree with above but i need such formula so that to appear the correct hour in decimals but NOT as a time format, so that to be able to prepare the total of them. Is there anyu option so that to "SUM" with time format?

Thanks