Time formatt

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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.

Originally Posted by Bash

I tried it. Its not helping.

"Anne Troy" wrote:

> Use this format instead, Bash:
> [h]:mm:ss
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "Bash" <[email protected]> wrote in message
> news:[email protected]...
> >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,

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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.



[QUOTE=Bash]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:
[color=blue]
>
> 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.
>
>

On Fri, 4 Nov 2005 18:58:01 -0800, "Bash" <[email protected]>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > >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" <[email protected]>
> 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
>