custom date format

[Lori B]

I am already using a custom date format of d:h:mm for a field where I am
calculating the networkdays from one date & time to another. I want to be
able to add these fields, however, if it exceeds 31 days it does not maintain
the number of days but starts over. How can I build a custom format to
recognize the total number of days? I have tried to put bracket around the
number of days to show as [d]:h:mm but it does not work. Any help would be
appreciated!

 

[Shane Devenshire]

Hi,

This is a rather unusual format d:h:mm, what do you want the results to look
like, give us an example.

d stands for single digit days and runs from 1 to 31 (the number of days in
a month). I don't think you can create a format which does exactly what you
want, but you can create a formula in a cell which display the way you want.

=INT(C4)&":"&TEXT(MOD(C4,1),"hh:mm")

where C4 is you cell you wanted to format.

 

[Ron Rosenfeld]

I am already using a custom date format of d:h:mm for a field where I am
calculating the networkdays from one date & time to another. I want to be
able to add these fields, however, if it exceeds 31 days it does not maintain
the number of days but starts over. How can I build a custom format to
recognize the total number of days? I have tried to put bracket around the
number of days to show as [d]:h:mm but it does not work. Any help would be
appreciated!

It would help if you would post your formula.

But the 'd' format token is used to express a portion of a DATE. Since there
are no months with more than 31 days, the 'd' token won't express that.

Your function should be returning a time, possibly expressed in days and
fractions of a day; and, without more information, it's not even clear if your
"day" is a 24 hour day.

If the result of your calculation is days and fractions of a day, you can add
them by simple addition, but I think we need to know more before giving further
advice.
--ron

 

[Lori B]

Here is an example. Cell d3 I have 02/02/2009 9:54. In cell D2 I have
02/09/2009 13:50. I have the time split out from each of those cells into
cell H2 (13:50) & h3 (9:54). My formula is
=networkdays(d3,d2,Lookup!$D:$D)-1+(h2-h3). I don't want to count the first
day so I have -1 in my formula to not include that. My results show in the
format of d:h:mm and for my example above would be 5:3:56. 5 days, 3 hours,
& 56 minutes. I use this to calculate the amount of time a job was in a
department. I then want to add up the total time of all jobs in that one
department. Does this help? Maybe I should be using a different format to
display the amount of time calculated per job? Thanks for your help

I am already using a custom date format of d:h:mm for a field where I am
calculating the networkdays from one date & time to another. I want to be
able to add these fields, however, if it exceeds 31 days it does not maintain
the number of days but starts over. How can I build a custom format to
recognize the total number of days? I have tried to put bracket around the
number of days to show as [d]:h:mm but it does not work. Any help would be
appreciated!

It would help if you would post your formula.

But the 'd' format token is used to express a portion of a DATE. Since there
are no months with more than 31 days, the 'd' token won't express that.

Your function should be returning a time, possibly expressed in days and
fractions of a day; and, without more information, it's not even clear if your
"day" is a 24 hour day.

If the result of your calculation is days and fractions of a day, you can add
them by simple addition, but I think we need to know more before giving further
advice.
--ron

 

[Ron Rosenfeld]

Here is an example. Cell d3 I have 02/02/2009 9:54. In cell D2 I have
02/09/2009 13:50. I have the time split out from each of those cells into
cell H2 (13:50) & h3 (9:54). My formula is
=networkdays(d3,d2,Lookup!$D:$D)-1+(h2-h3). I don't want to count the first
day so I have -1 in my formula to not include that. My results show in the
format of d:h:mm and for my example above would be 5:3:56. 5 days, 3 hours,
& 56 minutes. I use this to calculate the amount of time a job was in a
department. I then want to add up the total time of all jobs in that one
department. Does this help? Maybe I should be using a different format to
display the amount of time calculated per job? Thanks for your help

That clarifies things.

As far as I know, you won't be able to have both the format you want, and also
be able to use the result for math operations.

I would suggest that you either express your result as days and fraction of a
day, perhaps with a limited number of decimal places, or use a separate column
to display the results as text.

Your formula, in days, and fractions of a day, shows: 5.163888889

If that value is in, for example, G3, then:

=INT(G3)&TEXT(MOD(G3,1),":h:m")

would display your result in your preferred format. But you'll only be able to
do math operations on the value in G3.

You could also display the result as:

=INT(G3) & TEXT(MOD(G3,1),""" days, ""h"" hours, ""m"" minutes""")

which would be unambiguous to the casual perusor of the sheet.
--ron

 

[Lori B]

this helps. Thanks!!!

That clarifies things.

As far as I know, you won't be able to have both the format you want, and also
be able to use the result for math operations.

I would suggest that you either express your result as days and fraction of a
day, perhaps with a limited number of decimal places, or use a separate column
to display the results as text.

Your formula, in days, and fractions of a day, shows: 5.163888889

If that value is in, for example, G3, then:

=INT(G3)&TEXT(MOD(G3,1),":h:m")

would display your result in your preferred format. But you'll only be able to
do math operations on the value in G3.

You could also display the result as:

=INT(G3) & TEXT(MOD(G3,1),""" days, ""h"" hours, ""m"" minutes""")

which would be unambiguous to the casual perusor of the sheet.
--ron

 

[Ron Rosenfeld]

this helps. Thanks!!!

You're welcome.
--ron