Convert time clock ins (1/60) to 1/100 with formula

[Mers4B]

Hello! I am new here, but excited I found this forum. Your help is greatly needed! I just started at a new company and they had not been converting the numbers from 1/60 to 1/100 for the paychecks for over 6 months. There are a lot of employees and doing everyone by hand would take days. Could you help me figure out what formula is necessary to convert 1/60 to 1/100 automatically? Then get the difference necessary to pay the employee?

For example, Susan work 5.35 hours (this was put into payroll). It needs to be 5.83. Difference would be .48 minutes.

Is there any excel formula that would assist me?

​

 

[Oeldere]

Maybe you have to change the , (comma) into a . (dot) for your lokal settings.

Book1
	D	E
25	5,35	5,58
Blad13

Cell Formulas
Range	Formula
E25	=LEFT(D25,SEARCH(",",D25,1)-1)&","&INT(RIGHT(D25,2)/60*100)*1

 

[Mers4B]

Maybe you have to change the , (comma) into a . (dot) for your lokal settings.

	D	E

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]25[/TD]
[TD="align: right"]5,35[/TD]
[TD="align: right"]5,58[/TD]

</tbody>

Blad13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E25[/TH]
[TD="align: left"]=LEFT(D25,SEARCH(",",D25,1)-1)&","&INT(RIGHT(D25,2)/60*100)*1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for help! Do I have to change the commas in the formula as well? Currently, it is stating NO NAME. Below is the formula inserted with the box number changes...

[TABLE="width: 500"]
<tbody>[TR]
[TD]D2[/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD]5.35[/TD]
[TD]=LEFT(D2,SEARCH(",",D2,1)-1)&","&INT(RIGHT(D2,2)/60*100)*1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am not great with excel, so thanks for the help.

 

[Oeldere]

5.35 =LEFT(D2,SEARCH(",",D2,1)-1)&","&INT(RIGHT(D2,2)/60*100)*1

The red comma.

 

[SpillerBD]

So if 5.35 is 5 Hours 35 minutes and you want decimal time calculated...
=INT(D18)+(MOD(D18,1)*100/60)

(Are decimals greater than .59 checked as errors?)

 

[Rick Rothstein]

Maybe you have to change the , (comma) into a . (dot) for your lokal settings.

What comma? When I look at Message #1, I see a dot between the 5 and 35 and the 5 and 83. Anyway, assuming the decimal point is a dot, this formula should do the conversion the OP seeks...

=24*SUBSTITUTE(A1,".",":")

If the data really has a comma, then just change the dot in my formula to a comma.

 

[Mers4B]

Thank you all for the help, but SpillerBD's formula was perfect! Y'all just saved me hours and hours of work. Thanks so much!

 

[StephenCrump]

Thank you all for the help, but SpillerBD's formula was perfect! Y'all just saved me hours and hours of work. Thanks so much!

Problem solved ...

... so just for the record, you could also use the native Excel function:

=DOLLARDE(A1,60)

 

[Rick Rothstein]

... so just for the record, you could also use the native Excel function:

=DOLLARDE(A1,60)

Why do I keep forgetting about that function!

 

[SpillerBD]

Why do I keep forgetting about that function!

At least you forgot, I didn't even know !