Formatting decimal numbers as time - no conversion to time

[Guest]

I have a timesheet that's used for payroll purposes. The sheet is about as
simple as it can get.

I would like to be able to enter the time worked for XXXXXX as a decimal
number and have it display as hours and minutes. For example:

4.5 hours would display as 4:30
2.1 hours would display as 2:06

For any computation of the time, such as adding the two time examples above,
the calculations would work with the original decimal numbers, and the total
would yield 6.6 hours of time. Yet the display in the cell would be 6:36

You would think this would be a relatively easy thing to do, possibly with
the custom number format. But I've not been able to find any explanation of
the custom number formatting process that I understand, nor have I been able
to locate an example.

Is it easy to do? How do you do it? I would prefer NOT to have to have
hidden rows and columns, or any spiffy basic programming involved, as I would
like to pass on the finished product to others who know even less about Excel
than I do.


Thanks.

Ken

 

[Biff]

Well, to do what you want you will need either/or:

hidden rows and columns, or any spiffy basic programming involved

Biff

 

[Earl Kiosterud]

Ken,

You can't enter hours as a decimal number into a cell and have it display as
hh:mm in the same cell. Excel's date-time formatting is based on units of
days. The date-time formatting depends on it. 4.5 would always be 4 1/2
days.

Another cell can display it as hh:mm with:
=A2/24 where A2 contained the hours as decimal numbers. You'd format the
cell with Format - Cells - Number tab - Custom, and type in a format code
like: [hh]:mm

The total could be:
=SUM(A2:Awhatever)/24 formatted similarly as above. So if you're willing
to put your decimal numbers in separate cells, such as in a separate column,
you can do it. Consider that solution

For a good treatise on Excel's date-time formatting, see
http://www.cpearson.com/excel/datetime.htm#SerialDates

 

[Guest]

Biff and Earl,

Thanks for the replies, but heading down that road is not a good thing for
my situation. If I was going to be the only one using the timesheet, I'd do
it that way. But creating a sheet with those kinds of things would confuse a
lot of people that will ask me for a copy.

I just want it to look like time, not be treated like time. :-(

You would think that enough people here would have asked over time (oh, what
a bad pun!) for this ability that Microsoft would have built this in. It's
really a simple concept when you think about it.

Thanks again for the replies.



Ken