Hey all,

Does anyone know how to create the following custom number format?
56d, 5h, 4m

I have the number 56.2117988... calculated in a cell which translates to 56
days, 5 hours, and 4 minutes. I can get the cell to look like the above
format using several formulas mixed with text, but I would like to have this
remain a pure number for additional calculations.

The format dd"d", h"h", m"m" gives me 25d, 5h, 4m. I'm assuming 25 is day
of month (Feb). What is a custom number format that is total # of days, not
day of month, and yields 56d not 25d?

Thanks in advance.

Joel

Could you use a helper cell:

=INT(A1)&"d, "&TEXT(A1,"h\h\, m\m")



JP wrote:
>
> Hey all,
>
> Does anyone know how to create the following custom number format?
> 56d, 5h, 4m
>
> I have the number 56.2117988... calculated in a cell which translates to 56
> days, 5 hours, and 4 minutes. I can get the cell to look like the above
> format using several formulas mixed with text, but I would like to have this
> remain a pure number for additional calculations.
>
> The format dd"d", h"h", m"m" gives me 25d, 5h, 4m. I'm assuming 25 is day
> of month (Feb). What is a custom number format that is total # of days, not
> day of month, and yields 56d not 25d?
>
> Thanks in advance.
>
> Joel

--

Dave Peterson
(E-Mail Removed)

Dave,

I actually like your solution better than my current formula:
=ROUNDDOWN(A1,0) &"d, " & HOUR(A1) & "h, " & MINUTE(A1) & "m". But it still
makes it impossible to run calculations on that cell since it includes text.

My underlying question I guess is if there is a custom format to show
elapsed days rather than just days like you can do with hours ([hh] vs hh).

Thanks for you input Dave. I'm going to update my formula with your
suggestion unless there is a way to accomplish the above.

Joel

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Could you use a helper cell:
>
> =INT(A1)&"d, "&TEXT(A1,"h\h\, m\m")
>
>
>
> JP wrote:
> >
> > Hey all,
> >
> > Does anyone know how to create the following custom number format?
> > 56d, 5h, 4m
> >
> > I have the number 56.2117988... calculated in a cell which translates to
56
> > days, 5 hours, and 4 minutes. I can get the cell to look like the above
> > format using several formulas mixed with text, but I would like to have
this
> > remain a pure number for additional calculations.
> >
> > The format dd"d", h"h", m"m" gives me 25d, 5h, 4m. I'm assuming 25 is
day
> > of month (Feb). What is a custom number format that is total # of days,
not
> > day of month, and yields 56d not 25d?
> >
> > Thanks in advance.
> >
> > Joel
>
> --
>
> Dave Peterson
> (E-Mail Removed)

For some reason, Microsoft only gave that formatting to vba. Don't know why
it wasn't added to the worksheet. :>(

Function DHM(n) As String
DHM = Format(n + 1, "y\d h\h N\m")
End Function

?DHM(56.2117988)
56d 5h 4m

HTH
Dana DeLouis



"JP" <jp@wrs_.com> wrote in message
news:(E-Mail Removed)...
> Dave,
>
> I actually like your solution better than my current formula:
> =ROUNDDOWN(A1,0) &"d, " & HOUR(A1) & "h, " & MINUTE(A1) & "m". But it
> still
> makes it impossible to run calculations on that cell since it includes
> text.
>
> My underlying question I guess is if there is a custom format to show
> elapsed days rather than just days like you can do with hours ([hh] vs
> hh).
>
> Thanks for you input Dave. I'm going to update my formula with your
> suggestion unless there is a way to accomplish the above.
>
> Joel
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Could you use a helper cell:
>>
>> =INT(A1)&"d, "&TEXT(A1,"h\h\, m\m")
>>
>>
>>
>> JP wrote:
>> >
>> > Hey all,
>> >
>> > Does anyone know how to create the following custom number format?
>> > 56d, 5h, 4m
>> >
>> > I have the number 56.2117988... calculated in a cell which translates
>> > to
> 56
>> > days, 5 hours, and 4 minutes. I can get the cell to look like the
>> > above
>> > format using several formulas mixed with text, but I would like to have
> this
>> > remain a pure number for additional calculations.
>> >
>> > The format dd"d", h"h", m"m" gives me 25d, 5h, 4m. I'm assuming 25 is
> day
>> > of month (Feb). What is a custom number format that is total # of
>> > days,
> not
>> > day of month, and yields 56d not 25d?
>> >
>> > Thanks in advance.
>> >
>> > Joel
>>
>> --
>>
>> Dave Peterson
>> (E-Mail Removed)
>
>

Someday, I'm gonna remember those differences <vbg>.

But the OP will have that same calculation problem using the UDF.

Dana DeLouis wrote:
>
> For some reason, Microsoft only gave that formatting to vba. Don't know why
> it wasn't added to the worksheet. :>(
>
> Function DHM(n) As String
> DHM = Format(n + 1, "y\d h\h N\m")
> End Function
>
> ?DHM(56.2117988)
> 56d 5h 4m
>
> HTH
> Dana DeLouis
>
> "JP" <jp@wrs_.com> wrote in message
> news:(E-Mail Removed)...
> > Dave,
> >
> > I actually like your solution better than my current formula:
> > =ROUNDDOWN(A1,0) &"d, " & HOUR(A1) & "h, " & MINUTE(A1) & "m". But it
> > still
> > makes it impossible to run calculations on that cell since it includes
> > text.
> >
> > My underlying question I guess is if there is a custom format to show
> > elapsed days rather than just days like you can do with hours ([hh] vs
> > hh).
> >
> > Thanks for you input Dave. I'm going to update my formula with your
> > suggestion unless there is a way to accomplish the above.
> >
> > Joel
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Could you use a helper cell:
> >>
> >> =INT(A1)&"d, "&TEXT(A1,"h\h\, m\m")
> >>
> >>
> >>
> >> JP wrote:
> >> >
> >> > Hey all,
> >> >
> >> > Does anyone know how to create the following custom number format?
> >> > 56d, 5h, 4m
> >> >
> >> > I have the number 56.2117988... calculated in a cell which translates
> >> > to
> > 56
> >> > days, 5 hours, and 4 minutes. I can get the cell to look like the
> >> > above
> >> > format using several formulas mixed with text, but I would like to have
> > this
> >> > remain a pure number for additional calculations.
> >> >
> >> > The format dd"d", h"h", m"m" gives me 25d, 5h, 4m. I'm assuming 25 is
> > day
> >> > of month (Feb). What is a custom number format that is total # of
> >> > days,
> > not
> >> > day of month, and yields 56d not 25d?
> >> >
> >> > Thanks in advance.
> >> >
> >> > Joel
> >>
> >> --
> >>
> >> Dave Peterson
> >> (E-Mail Removed)
> >
> >

--

Dave Peterson
(E-Mail Removed)

Too bad. Thanks for the input.

Joel

"Dana DeLouis" <(E-Mail Removed)> wrote in message
news:uT1$(E-Mail Removed)...
> For some reason, Microsoft only gave that formatting to vba. Don't know
why
> it wasn't added to the worksheet. :>(
>
> Function DHM(n) As String
> DHM = Format(n + 1, "y\d h\h N\m")
> End Function
>
> ?DHM(56.2117988)
> 56d 5h 4m
>
> HTH
> Dana DeLouis
>
>
>
> "JP" <jp@wrs_.com> wrote in message
> news:(E-Mail Removed)...
> > Dave,
> >
> > I actually like your solution better than my current formula:
> > =ROUNDDOWN(A1,0) &"d, " & HOUR(A1) & "h, " & MINUTE(A1) & "m". But it
> > still
> > makes it impossible to run calculations on that cell since it includes
> > text.
> >
> > My underlying question I guess is if there is a custom format to show
> > elapsed days rather than just days like you can do with hours ([hh] vs
> > hh).
> >
> > Thanks for you input Dave. I'm going to update my formula with your
> > suggestion unless there is a way to accomplish the above.
> >
> > Joel
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Could you use a helper cell:
> >>
> >> =INT(A1)&"d, "&TEXT(A1,"h\h\, m\m")
> >>
> >>
> >>
> >> JP wrote:
> >> >
> >> > Hey all,
> >> >
> >> > Does anyone know how to create the following custom number format?
> >> > 56d, 5h, 4m
> >> >
> >> > I have the number 56.2117988... calculated in a cell which translates
> >> > to
> > 56
> >> > days, 5 hours, and 4 minutes. I can get the cell to look like the
> >> > above
> >> > format using several formulas mixed with text, but I would like to
have
> > this
> >> > remain a pure number for additional calculations.
> >> >
> >> > The format dd"d", h"h", m"m" gives me 25d, 5h, 4m. I'm assuming 25
is
> > day
> >> > of month (Feb). What is a custom number format that is total # of
> >> > days,
> > not
> >> > day of month, and yields 56d not 25d?
> >> >
> >> > Thanks in advance.
> >> >
> >> > Joel
> >>
> >> --
> >>
> >> Dave Peterson
> >> (E-Mail Removed)
> >
> >
>
>