Good day, people. I was hoping someone would be able to help me out with
a rather simple issue with regards to custom cell formats in Excel. I'm
creating a budget sheet for work purposes that needs a breakdown in
both Man Days and Crew Days (a crew being six men). These numbers are
extracted by calculations on a cell that contains a lump sum number of
Man Hours.

Well, what I need for easy understanding/readability for field managers
is a format for Man Days that would read "##d ##h". For example, dealing
with the number 4782 - 4,782 Man Hours - I'd like the Man Days
calculation to display the result as '597d 6h". This is something I can
do in actuality but not in a good way. My current method is as follows:

A1: 4782
A2: =(INT((A1/24)*3)&"d "&ROUNDDOWN(MOD(A1,24),0)&"h")

And bingo, it spits out the exact format I need. The problem with this
result is it cannot be calculated because it is no longer a valid
numerical value, and I'd like to be able to use these values in other
aspects of the sheet, such as for SUM formulas. And there are ways
around this as well, such as referring back to the Man Hours cell but
I'd rather not. I'd like to keep everything neat, in-line, and direct,
without overlapping references.

I've tried to correct this using the custom cell formats but haven't
been able to get good results. I've tried variations on themese like
"##\d .##\h" but that leaves a decimal and I really need the remainder
to equate to an actual eight-hour work day. Is there possibly away to
make these custom formats handle simple math, like the fractions
format? The format "#\d ??/??\h" results in "597d 3/4h", which is kind
of along the lines of what I'm trying to accomplish but not quite -
however, it's basically performing a division calculation in that
format.

Is there a custom format possible that I haven't guessed yet, a way to
develop such a custom format, or a way to work the actual cell formulas
out so that the result can still be tabulated by later formulas? Thanks
in advance, everyone, for having a look at this.


--
centerNegative
------------------------------------------------------------------------
centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921
View this thread: http://www.excelforum.com/showthread...hreadid=384618