I work for a call center where in a process I keep the track of time duration
of calls. I want to write a time durations in the formatt of hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12 minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I go to
formatt and change the data type to hh:mm:ss it remains the same.

Use this format instead, Bash:
[h]:mm:ss
************
Anne Troy
www.OfficeArticles.com

"Bash" wrote in message
...
I work for a call center where in a process I keep the track of time
duration
of calls. I want to write a time durations in the formatt of hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12 minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I go to
formatt and change the data type to hh:mm:ss it remains the same.

I tried it. Its not helping.

"Anne Troy" wrote:

Use this format instead, Bash:
[h]:mm:ss
************
Anne Troy
www.OfficeArticles.com

"Bash" wrote in message
...
I work for a call center where in a process I keep the track of time
duration
of calls. I want to write a time durations in the formatt of hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12 minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I go to
formatt and change the data type to hh:mm:ss it remains the same.

Bash,

I tried Anne's suggestion and, at first, it didn't work, Excel did not
want to keep the improper format.

Then I read the 'Convert' function and loaded the Add-in Analysis
ToolPak (Tools - Add-ins..) and closed / re-opened the Workbook.

Now it shows the 53 hours in Anne's [h]:mm:ss format.



Bash Wrote:
I tried it. Its not helping.

"Anne Troy" wrote:

Use this format instead, Bash:
[h]:mm:ss
************
Anne Troy
www.OfficeArticles.com

"Bash" wrote in message
...
I work for a call center where in a process I keep the track of
time
duration
of calls. I want to write a time durations in the formatt of
hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12
minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I
go to
formatt and change the data type to hh:mm:ss it remains the same.





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=482408

Bryan,

I added the Analysis ToolPak, changed the format to [h]:mm:ss and then
closed and opened the excel. Its not happening, I still see it in '1/2/1900
5:12:49 AM' formatt in the formula bar . I did not quite understand how
'Convert' function should be used is this. May be that's where I am going
wrong.

Bash

"Bryan Hessey" wrote:


Bash,

I tried Anne's suggestion and, at first, it didn't work, Excel did not
want to keep the improper format.

Then I read the 'Convert' function and loaded the Add-in Analysis
ToolPak (Tools - Add-ins..) and closed / re-opened the Workbook.

Now it shows the 53 hours in Anne's [h]:mm:ss format.



Bash Wrote:
I tried it. Its not helping.

"Anne Troy" wrote:

Use this format instead, Bash:
[h]:mm:ss
************
Anne Troy
www.OfficeArticles.com

"Bash" wrote in message
...
I work for a call center where in a process I keep the track of
time
duration
of calls. I want to write a time durations in the formatt of
hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12
minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I
go to
formatt and change the data type to hh:mm:ss it remains the same.





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=482408

Bash,

No idea why it isn't working for you, nor what the AddIn does, just
noticed it in testing.

I re-checked and do have some cells formatted for [hhh]:mm:ss and some
as [h]:mm:ss, both work as expected, with improper hours.
I didn't use the 'Convert', was just reading up on that when I fond the
reference to the ToolPak

That is in Excel 2003 SP1.



Bash Wrote:[color=blue]
Bryan,

I added the Analysis ToolPak, changed the format to [h]:mm:ss and then
closed and opened the excel. Its not happening, I still see it in
'1/2/1900
5:12:49 AM' formatt in the formula bar . I did not quite understand
how
'Convert' function should be used is this. May be that's where I am
going
wrong.

Bash

"Bryan Hessey" wrote:


Bash,

I tried Anne's suggestion and, at first, it didn't work, Excel did
not
want to keep the improper format.

Then I read the 'Convert' function and loaded the Add-in Analysis
ToolPak (Tools - Add-ins..) and closed / re-opened the Workbook.

Now it shows the 53 hours in Anne's [h]:mm:ss format.




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=482408

On Fri, 4 Nov 2005 18:58:01 -0800, "Bash"
wrote:

I work for a call center where in a process I keep the track of time duration
of calls. I want to write a time durations in the formatt of hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12 minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I go to
formatt and change the data type to hh:mm:ss it remains the same.

You'll have to ignore what you see in the formula bar.

Format the cell as [h]:mm:ss.

Any mathematical operations will work OK.

So far as Excel is concerned, 53:12:49 is stored as 2.21723380 and is
equivalent to 1/2/1900 5:12:49 AM


--ron

Bash: You will always see that in the formula bar. Excel stores times and
dates in that format regardless. If you want it to be able to add up those
values, you have no choice really. I'm pretty sure I'm correct about this.
If you don't, you could probably put a custom format on the cell and enter
the numbers without the colons. Try: 00":"00":"00 in the custom format.
You may need to read Chip's article:
http://www.cpearson.com/excel/datetime.htm#SerialDates
************
Anne Troy
www.OfficeArticles.com

"Bash" wrote in message
...
Bryan,

I added the Analysis ToolPak, changed the format to [h]:mm:ss and then
closed and opened the excel. Its not happening, I still see it in
'1/2/1900
5:12:49 AM' formatt in the formula bar . I did not quite understand how
'Convert' function should be used is this. May be that's where I am going
wrong.

Bash

"Bryan Hessey" wrote:


Bash,

I tried Anne's suggestion and, at first, it didn't work, Excel did not
want to keep the improper format.

Then I read the 'Convert' function and loaded the Add-in Analysis
ToolPak (Tools - Add-ins..) and closed / re-opened the Workbook.

Now it shows the 53 hours in Anne's [h]:mm:ss format.



Bash Wrote:
I tried it. Its not helping.

"Anne Troy" wrote:

Use this format instead, Bash:
[h]:mm:ss
************
Anne Troy
www.OfficeArticles.com

"Bash" wrote in message
...
I work for a call center where in a process I keep the track of
time
duration
of calls. I want to write a time durations in the formatt of
hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12
minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I
go to
formatt and change the data type to hh:mm:ss it remains the same.





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=482408

Ron, Bryan, Anne,

Thanks for your help. It was great researching something that has troubled
me for long. Although the format [hh:mm:ss] that I use allows me to perform
any mathematical operation. However, what troubled me was the way it appears
in the formula bar, I always wondered how could I have the formula bar say
what I mean
Ron has settled it by saying it will remain the same.

Thanks again.

"Ron Rosenfeld" wrote:

On Fri, 4 Nov 2005 18:58:01 -0800, "Bash"
wrote:

I work for a call center where in a process I keep the track of time duration
of calls. I want to write a time durations in the formatt of hh:mm:ss.
However if I write say 53:12:49 (where it should mean 53 hours, 12 minutes
and 49 seconds) excel takes it as 1/2/1900 5:12:49 AM. Even if I go to
formatt and change the data type to hh:mm:ss it remains the same.

You'll have to ignore what you see in the formula bar.

Format the cell as [h]:mm:ss.

Any mathematical operations will work OK.

So far as Excel is concerned, 53:12:49 is stored as 2.21723380 and is
equivalent to 1/2/1900 5:12:49 AM


--ron