How to convert decimal number to minutes

[jinasalcedo]

I am tracking paid time off in excel. We have been using a decimal fraction of a minutes like .5 fro 30 minutes. But since we started using quickbooks payroll, qbs used 30 minutes (1:30) for 1.5.

How do I convert my data from 1.5 to 1:30 in excel spreadsheet? Any idea?

Thanks.

 

[VoG]

Try

=A1/1440


Format as Time.

 

[jinasalcedo]

1.5 came out as 0:01 when I did =A1/1440

should be 1:30

Thanks Vog .. nice try though.

 

[VoG]

Make sure that you are displaying the value correctly

Excel Workbook
	A	B
1	1.5	00:01:30
Sheet5

 

[jinasalcedo]

Got it! I changed the format to hh:mm:ss

Thank you very much Vog! Saved a lot of time..

 

[jinasalcedo]

Me again..it didn't calculate for other cells, only the first one. See below;
33.98 to 33:58 is correct but the rest are wrong.

<table border="0" cellpadding="0" cellspacing="0" width="230"><col style="width: 59pt;" width="78"> <col style="width: 48pt;" width="64"> <col style="width: 66pt;" width="88"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 59pt;" height="17" width="78">Ending Balance</td> <td class="xl65" style="width: 48pt;" width="64">
</td> <td class="xl69" style="width: 66pt;" width="88">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">33.98</td> <td class="xl65">
</td> <td class="xl69" align="right">33:58.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">33.98</td> <td class="xl65">
</td> <td class="xl69" align="right">33:58.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17"> </td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt;" height="19">
</td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt;" height="19">
</td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">Ending Balance</td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">113.62</td> <td class="xl65">
</td> <td class="xl69" align="right">53:36.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">167.69</td> <td class="xl65">
</td> <td class="xl69" align="right">47:41.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt;" height="19">
</td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt;" height="19">
</td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">Ending Balance</td> <td class="xl65">
</td> <td class="xl69">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">179.47</td> <td class="xl65">
</td> <td class="xl69" align="right">59:28.3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="border-top: medium none; height: 12.75pt;" height="17">240.00</td> <td class="xl65">
</td> <td class="xl69" align="right">00:00.0</td> </tr> </tbody></table>

 

[VoG]

Format the result cell as Custom [h]:mm:ss

 

[jinasalcedo]

I did but still not right. I noticed that if the hours is below 60, the calculation is correct but if the hours is more than 60, it is calculating incorrectly.

113.62 is 113 hrs and .37 minutes , should have been 113:37, right?

 

[jasonb75]

Should be =A1/60 not =A1/1440

You were converting to minutes and seconds not hours and minutes.

 

[VoG]

Works here

Excel Workbook
	A	B
1	113.62	1:53:37
Sheet5