Custom number format

[JP]

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]

Could you use a helper cell:

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

 

[JP]

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

 

[Dana DeLouis]

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

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]

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

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

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

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

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

 

[JP]

Too bad. Thanks for the input.

Joel

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

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

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