Convert decimal hours to hours:minutes

  • Thread starter Thread starter Mike Mead
  • Start date Start date
M

Mike Mead

How can I convert manhours to hours and minutes? The
Knowledge Base says use n/24 (where n=decimal hours),
then format/cell/time/hh:mm.

But this only works for hours less than 24. For example,
total manhours for a job is 28.5, then answer in hours
and minutes should be 28:30. But Excel 2003 gives the
answer as 4:30.
 
Trevor, Thanks very much!

I wonder why this does not appear in the time format list?

Regards

Mike
-----Original Message-----
Mike

format the cell as [hh]:mm

Regards

Trevor


Mike Mead said:
How can I convert manhours to hours and minutes? The
Knowledge Base says use n/24 (where n=decimal hours),
then format/cell/time/hh:mm.

But this only works for hours less than 24. For example,
total manhours for a job is 28.5, then answer in hours
and minutes should be 28:30. But Excel 2003 gives the
answer as 4:30.
Click to expand...


.
Click to expand...
 
It does, at least for excel 2002/2003
under format>cells>number>time and scroll down to 37:30:55
It would be better IMHO if the time values greater than 24:00
summed together and excel would automatically use that format
It does that format if you type in more than 24 hours

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Trevor, Thanks very much!

I wonder why this does not appear in the time format list?

Regards

Mike
-----Original Message-----
Mike

format the cell as [hh]:mm

Regards

Trevor


Mike Mead said:
How can I convert manhours to hours and minutes? The
Knowledge Base says use n/24 (where n=decimal hours),
then format/cell/time/hh:mm.

But this only works for hours less than 24. For example,
total manhours for a job is 28.5, then answer in hours
and minutes should be 28:30. But Excel 2003 gives the
answer as 4:30.
Click to expand...


.
Click to expand...
Click to expand...
 
Mike

I guess that strictly speaking it's not a time, it's a number of hours and
minutes.

Regards

Trevor


Trevor, Thanks very much!

I wonder why this does not appear in the time format list?

Regards

Mike
-----Original Message-----
Mike

format the cell as [hh]:mm

Regards

Trevor


Mike Mead said:
How can I convert manhours to hours and minutes? The
Knowledge Base says use n/24 (where n=decimal hours),
then format/cell/time/hh:mm.

But this only works for hours less than 24. For example,
total manhours for a job is 28.5, then answer in hours
and minutes should be 28:30. But Excel 2003 gives the
answer as 4:30.
Click to expand...


.
Click to expand...
Click to expand...
 
I had this problem when using Excel for timesheets. You
CAN use normal hours:minutes entries and they will give
the correct answer when adding over 24 hrs if you use the
following cell format.

Select Custom then set the cell format to: [h]:mm

the use of the square bracket makes excel add the hours
correctly. It works with ver 2000, presumable it will also
work with other versions.

Regards
 
Back
Top