greetings all,
I have a file where i am tracking time. periodically a new line is added
with the new time. for reason difficult to explain, periodically, the time
gets reset to zero i.e. starts over. currently i am using format dd:hh:mm:ss
this works well but occasionally the time runs beyond 30 days(1 month) and
the above format resets to day 1 when it goes beyond 30 day(1 month?). i have
tried dilffernts format to try and make the days roll over ie 35:00:00:00 but
to no avail.
[dd]:hh:mm:ss doesn't work.
can someone give me some pointers/tips/directions on my dilima.

help would be most appreciated.
regards
FSt1

The dd format code returns the day of month for the value. Therefore,
the formated dd value will always be between 1 and 31. The day of
month begins at 1-Jan-1900, so after 31 days, it will roll back to 1,
indicating Feb 1, 1900. The dd format code does not support the
square bracket modifiers as do the hh and mm format codes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 11 Dec 2009 14:47:01 -0800, FSt1
wrote:

greetings all,
I have a file where i am tracking time. periodically a new line is added
with the new time. for reason difficult to explain, periodically, the time
gets reset to zero i.e. starts over. currently i am using format dd:hh:mm:ss
this works well but occasionally the time runs beyond 30 days(1 month) and
the above format resets to day 1 when it goes beyond 30 day(1 month?). i have
tried dilffernts format to try and make the days roll over ie 35:00:00:00 but
to no avail.
[dd]:hh:mm:ss doesn't work.
can someone give me some pointers/tips/directions on my dilima.

help would be most appreciated.
regards
FSt1

If you need the format for display only, not as a value that can be
used in calculation, the following formula will return a string of the
format dd:hh:mm:ss for the value in A1.

=INT(A1)&":"&TEXT(A1-INT(A1),"hh:mm:ss")

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 11 Dec 2009 14:47:01 -0800, FSt1
wrote:

greetings all,
I have a file where i am tracking time. periodically a new line is added
with the new time. for reason difficult to explain, periodically, the time
gets reset to zero i.e. starts over. currently i am using format dd:hh:mm:ss
this works well but occasionally the time runs beyond 30 days(1 month) and
the above format resets to day 1 when it goes beyond 30 day(1 month?). i have
tried dilffernts format to try and make the days roll over ie 35:00:00:00 but
to no avail.
[dd]:hh:mm:ss doesn't work.
can someone give me some pointers/tips/directions on my dilima.

help would be most appreciated.
regards
FSt1

hi
thanks chip. coming from you that more or less sez it. bummer.

regards
FSt1

"Chip Pearson" wrote:

The dd format code returns the day of month for the value. Therefore,
the formated dd value will always be between 1 and 31. The day of
month begins at 1-Jan-1900, so after 31 days, it will roll back to 1,
indicating Feb 1, 1900. The dd format code does not support the
square bracket modifiers as do the hh and mm format codes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 11 Dec 2009 14:47:01 -0800, FSt1
wrote:

greetings all,
I have a file where i am tracking time. periodically a new line is added
with the new time. for reason difficult to explain, periodically, the time
gets reset to zero i.e. starts over. currently i am using format dd:hh:mm:ss
this works well but occasionally the time runs beyond 30 days(1 month) and
the above format resets to day 1 when it goes beyond 30 day(1 month?). i have
tried dilffernts format to try and make the days roll over ie 35:00:00:00 but
to no avail.
[dd]:hh:mm:ss doesn't work.
can someone give me some pointers/tips/directions on my dilima.

help would be most appreciated.
regards
FSt1
.

hi
your formula works good but unfortunely..off to the side. maybe i can add a
column, dump the times in a hidden column and display the "other" times in
the new column. our engineering department seem to be sexually stimulated by
numbers so maybe they will get off on all the extra numbers. i'll work on it.
thanks for the inside. you have taught me something.

Regards
FSt1

"Chip Pearson" wrote:

If you need the format for display only, not as a value that can be
used in calculation, the following formula will return a string of the
format dd:hh:mm:ss for the value in A1.

=INT(A1)&":"&TEXT(A1-INT(A1),"hh:mm:ss")

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 11 Dec 2009 14:47:01 -0800, FSt1
wrote:

greetings all,
I have a file where i am tracking time. periodically a new line is added
with the new time. for reason difficult to explain, periodically, the time
gets reset to zero i.e. starts over. currently i am using format dd:hh:mm:ss
this works well but occasionally the time runs beyond 30 days(1 month) and
the above format resets to day 1 when it goes beyond 30 day(1 month?). i have
tried dilffernts format to try and make the days roll over ie 35:00:00:00 but
to no avail.
[dd]:hh:mm:ss doesn't work.
can someone give me some pointers/tips/directions on my dilima.

help would be most appreciated.
regards
FSt1
.