Time formatt

[Guest]

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.

 

[Anne Troy]

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

 

[Guest]

I tried it. Its not helping.

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

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]

Bash,

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

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

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

I tried it. Its not helping.

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

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

 

[Guest]

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

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.

I tried it. Its not helping.

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

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]

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.

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

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.

 

[Ron Rosenfeld]

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

 

[Anne Troy]

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

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

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.

I tried it. Its not helping.

:

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

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?action=getinfo&userid=21059
View this thread:
http://www.excelforum.com/showthread.php?threadid=482408

 

[Guest]

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.

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