cells with hours, minutes, and seconds

[Adam Sandler]

Hello, I have a column labeled "task time". In my spreadsheet, I have
a requirement to track how long a job's tasks take and come up with a
total elapsed time for all the work.

So if I have a manufacturing process which takes 61 seconds to set up
and it runs for 8 seconds, On row 1, I want to enter 00:01:01. On row
two I want to enter 00:00:08. and so on and so on until I get to the
end of the rows and total that column.

The problem is whenever I enter 00:01:01, 00:00:08, etc., then a date
gets appended to my entry!!! I even tired to apply a custom mask, like
hh:mm:ss or mm:ss, to the cell's format and the date still gets placed
in my cell!

How can I prevent this from happening? How can I have a time type and
not have a date to go along with it?

Thanks!

 

[rspowell]

Hello, I have a column labeled "task time".  In my spreadsheet, I have
a requirement to track how long a job's tasks take and come up with a
total elapsed time for all the work.

So if I have a manufacturing process which takes 61 seconds to set up
and it runs for 8 seconds, On row 1, I want to enter 00:01:01.  On row
two I want to enter 00:00:08.  and so on and so on until I get to the
end of the rows and total that column.

The problem is whenever I enter 00:01:01, 00:00:08, etc., then a date
gets appended to my entry!!! I even tired to apply a custom mask, like
hh:mm:ss or mm:ss, to the cell's format and the date still gets placed
in my cell!

How can I prevent this from happening?  How can I have a time type and
not have a date to go along with it?

Thanks!

Adam --

Although, I'm not sure exactly what you're describing by the date
being appended, maybe this will help some ...

* You may need to format the entire column -- if you are adding new
records that seem to have a different format from what you expect

* Excel stores Time as serial values -- the value of a Day is 1, and
time is a decimal fraction of a day

Using your entries for example ...

00:01:01 formatted as hh:mm:ss -- Excel handles this as .
000706018518518518

-and-

00:00:08 to Excel is .0000925925925926

... because these this is what part of a 24-hour day that those
values represent

The Date part is to the left of the decimal -- the Time part is to
the right of the decimal

So, regardless of how the cell is formatted, this is what is stored

Now -- if your entry did not include a Date -- it's still a
fraction of the 1st date Excel knows, which is 1/1/1900

Hopefully this sheds enough light on the matter to help you work
out a solution


- Rodney POWELL
Microsoft MVP - Excel

www.BeyondTechnology.com

 

[Adam Sandler]

Thanks for the reply... I'm sorry but I don't understand your post.
Perhaps a different explanation of my dilemma will help;

I want to add time... as in duration, not the period of a day. Lets
say your reading a cookbook. It says to back for 15 minutes... simply
defined as that task takes that long. The cookbook doesn't say cook
for fifteen minutes on 1/1/1900 starting at 00:00:00.

Let's say I have a cell and I put five minutes in there. Lets say I
have another cell and I put 6 minutes in there. What is 00:05:00 +
00:06:00? It should be eleven minutes right??? Instead, this is what
Excel returns when I do a sum of those two cells: 243:36:00

And I believe this is because there is a date component being appended
to my entry despite the fact I explicitly formatted those cells with a
mask of hh:mm:ss.

Thanks again for your time thus far.

 

[David Biddulph]

Temporarily format your 0:5:0 and 0:6:0 cells as General and tell us what
numbers you see there.
243:36 comes out as 10.15, i.e. 10.15 days, so it would be interesting to
see what values you are adding to get that total.

If you are commenting on a previous reply it is better to quote enough of
that message to put your reply into context.

Excel does not draw a distinction between dates and times in storing
durations. All times are stored in multiples of a day, so 2.4 hours
(2:24:00) is 0.1 day. You can choose to display a number as any combination
you choose of date and time. If you choose to include the date portion, the
time counts from 1/1/1900, with a slight distortion at 29/2/1900 to be
compatible with earlier spreadsheet software. Just be aware that if I store
current date and time in Excel it is stored (for me now) as 39894.29665. If
I choose to display as hh:mm:ss it displays as 07:07:10, but the 39894 full
days are still included in the number stored. If you want to display as the
full number of hours minutes and seconds, put square brackets in the
fomatting of the hours, so [h]:mm:ss displays as 957463:07:10. If you want
to remove the whole days from the number stored, use =MOD(A2,1) to change
the number from 39894.29665 to 0.29665.

 

[Adam Sandler]

Temporarily format your 0:5:0 and 0:6:0 cells as General and tell us what
numbers you see there.
243:36 comes out as 10.15, i.e. 10.15 days, so it would be interesting to
see what values you are adding to get that total.

Wow... general formatting works. I guess I never gave General a
chance as when I saw Excel automatically appending a date to my entry,
I panicked and tried to explicitly apply a format mask to the cells.

Thanks everyone!

 

[David Biddulph]

You didn't tell us what values you had. Those who were trying to help you
would like to know what the answer was, and the archives of the group try to
help other readers with similar problems in future.
--
David Biddulph

Temporarily format your 0:5:0 and 0:6:0 cells as General and tell us what
numbers you see there.
243:36 comes out as 10.15, i.e. 10.15 days, so it would be interesting to
see what values you are adding to get that total.

Wow... general formatting works. I guess I never gave General a
chance as when I saw Excel automatically appending a date to my entry,
I panicked and tried to explicitly apply a format mask to the cells.

Thanks everyone!

 

[Adam Sandler]

You didn't tell us what values you had.  

Sure I did. Look at the previous posts; 00:05:00 and 00:06:00 are the
values. When the cells have general formatting, the answer is
00:11:00; which is exactly what I was looking for.

Those who were trying to help you
would like to know what the answer was, and the archives of the group tryto
help other readers with similar problems in future.

Please make sure that when you call someone out for a supposed breach
of USENET etiquette, that you don't freaking TOP POST when you do it!