Duration in Excel - format 4

[Jeanie]

I am trying to format a report that shows the length of time an action took. I want the format to be in hours:minutes:seconds (00:03:37). I can get the cells to show this, but the formula bar at the top show 12:03:37 a.m.). This confuses the users. Is there anyway to get the bar to actually show 00:03:37?

Thanks,

Jeanie

 

[xlbo]

hwat formula are you using, what are the inputs and how is the result formatted ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

You can HIDE the formula using Format > Cells - Protection Tab: HIDDEN.

THEN protect the sheet.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Jeanie]

I am actually not using a formaula. My input is just 00:03:37 - which is what the cell shows, but the bar up at the top that shows the data (the formula bar - I think) shows it as 12:03:37 a.m. They want the bar to actually show 00:03:37.

Sorry to be so confusing!

 

[SkipVought]

no confusion.

Waht I suggested will work, formula or not.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[xlbo]

hmmm - how are you entering the data and what is the cell formatted as?

When I enter 00:03:37 into a blank cell in excel, it formats it as hh:mm:ss and that is what is displayed in the formula bar (xl2003)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

geoff,

I see 12:03:37 AM too

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Jeanie]

I am using Excel 2003 if that makes a difference.

I do not think protecting the sheet will work because it is being forwarded to others to manipulate.

thx

 

[SkipVought]

Sure it will, as long as you UNLOCK all cells. Don't use a password. No one will know the difference.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[yogia]

I am trying to format a report that shows the length of time an action took. I want the format to be in hours:minutes:seconds (00:03:37). I can get the cells to show this, but the formula bar at the top show 12:03:37 a.m.). This confuses the users. Is there anyway to get the bar to actually show 00:03:37?

Thanks,

Jeanie

Hi Jeanie:

One way would to preformat the related cell, column, or range as TEXT before making the entry 00:03:37

It will then display as 00:03:37 in the Formula Bar, and you will still be able to use this entry in calculations.

Would this work you? If you have a different situation in bigger scheme of things, please post back and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Jeanie]

Thanks, everyone. Yogia's suggestion works perfectly for my situation!

Jeanie

 

[1726]

Excel's formula bar shows times in the time format defined in the Regional and Language Settings in the Windows Control Panel.

Select: HH:mm:ss in place of hh:mm:ss tt
the tt element gives rise to the Am/Pm format.

The change will take effect next time you launch Excel.

 

[SkipVought]

Thanx 1726. ==> *

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Jeanie]

Thanks 1726 - I would have never figured that out!

Jeanie