MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Converting time from 3:30 to 3.50

Posted by Jade on June 01, 2000 10:29 AM

I need help converting time from 3:30 to 3.50.
Payroll needs it to be in 3.5 format, but I can
only get the paysheet to do it in 3:30.

Posted by Tim Francis-Wright on June 01, 0100 1:32 PM

If you have the time in hours:minutes in column A,
then column B could have formulas in this format:

[B1] = HOUR(A1)+ MINUTE(A1)/60

This converts to decimalized hours. But do
take care not to try to add columns A and B
together: 3:30 is the same to Excel as 0.15,
not 3.50.

Posted by Jade on June 01, 0100 1:48 PM

Thank you!! That works fine except when I go over 24 hours.
Do you have any suggestions for going up to 200 hours??

Posted by Ivan Moala on June 01, 0100 3:33 PM

you could try
=( HOUR(F15)+INT((F15))*24)+ MINUTE(F15)/60


Posted by Ashok Namjoshi on June 07, 0100 7:30 PM

1. Converting time from 3:30 to 3.5:
[time_cell]*24 will give you 3.5 in decimal format.

2. If you want to show hours>=24:00, then use Format-Cells-Custom, and put [hh]:mm and it will show you any hours >= 24.

Hope it helps.