tracking time

  • Thread starter Thread starter FSt1
  • Start date Start date
F

FSt1

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]
 
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]
 
hi
thanks chip. coming from you that more or less sez it. bummer.

regards
FSt1

Chip Pearson said:
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]



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 said:
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]



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
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

time calculations 2
years-months-days-hours 5
calc time 3
Date / Time problem 4
How many are at work every hour in a 24-hour day? 2
Formating time 2
Calcuating Elapsed Time 1
Problems with Time!! 3

Back
Top