Custom Formats

[centerNegative]

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.

 

[swatsp0p]

I'm not sure if this will meet your needs, but a formula of =A1/8 and a
Custom Format as such:

#\d # \h /8

will return your number of 4782 as "*597d 6 h /8*" and will be
available for subsequent math actions.

note: the /8 will be visible but simply indicates an 8 hour day and
does not hamper calculations on the value contained within the cell.

also note that even days (eg 4784 will return simply "598" and 8
returns "1") and less than one day (e.g. 5) returns "5 h /8"

Does this work for you?

 

[centerNegative]

Yes, that is indeed one way to do it but I'm trying to keep it in a
simple time format and do away with fractions and decimals. All the
numbers need to be easily equatable to actual units of time, as the
people involved in looking at these figures for their schedules aren't
the best and brightest and, even when they are, don't like being
confronted with math.

Is this maybe a problem that could be solved with VBscript?

 

[centerNegative]

I'm still working on this in order to come up with the desired format of
[N]d [N]h, so I'm doing the bump thing to see if there are any fresh,
new ideas. I had to make a slight correction in the formula because I
noticed it wasn't dispaying the eight-hour remainder correctly.

So, here's what I am faced with once again: I'm trying to take a total
number of man hours and convert it into man days, based on an eight
hour work day, with the remaining hours displaying as well. In other
words, I have the following data:

A1: 405.20h* [#,##0.00\h]*
A2: =IF(A1>0,(INT((A1/24)*3)&"d "&ROUNDDOWN(MOD(A1,24/3),0)&"h"),"")

The main parts of the formula are colored; I used a rounding function
to keep things simple, and wrapped it all in an IF statement to ensure
the cell would just remain blank if there was no man hour value to
calculate, instead of displaying zeros.

That formula displays the result, correctly, as 50d 5h, or 50
eight-hour man days, and 5 man hours. The problem I'm having is my
formula essentially turns the value in A1 into a text value in A2 which
can no longer be used in calculations. I'm pulling my hair out trying to
display that exact same value, "50d 5h", as a custom number format so
it'll still remain an actual number value that can be processed in cell
calculations.

As always, I appreciate every reply, thanks in advance for everyone's
help!