Excel Time Format

[GoPens]

Hi All,

I am looking to format a column for time i.e. minutes:seconds.
I've tried custom formatting etc. no luck.

Any help would be appreciated.

Thanks.

 

[SkipVought]

Hi,

If you format a TIME cell as GENERAL, what VALUE do you see? It should be a decimal NUMBER.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GoPens]

Yes, it does display a decimal. So, when I need to calculate, how would I go about formatting it back into time (mm:ss).

Thanks.

 

[SkipVought]

{b]Format > Cells - Number TAB -- Time (select the appropriate TIME format)[/b]

FYI: faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GoPens]

Thanks for the quick reply Skip. I have formatted the cell to MM:SS@ hoping for time display 10:12 example. Unfortunately, when I enter 25:12 as an example, Excel will convert to 12:00.

Thanks.

 

[SkipVought]

25:12 is 25 HOURS, 12 MINUTES.

It will not display beyond 24 hours, unless you format...
[tt]
[hh]:mm:ss
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Then you need to enter 00:25:12, because Excel is interpreting your data as hh:mm

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[GoPens]

You are correct sir. It is suppose to be 25 minutes and 12 seconds. When I attempted to format, the format would not take.

Thanks.

 

[SkipVought]

You must enter HOURS : MINUTES : SECONDS

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

FYI,

When you enter a whole number, followed by a COLON, Excel assumes that the FIRST number is HOURS. You don't even have to enter anything else.

Excel takes the First number as HOURS, the number after the colon as MINUTES, if its a WHOLE number and the number after the second COLON as SECONDS, and then CONVERTS those values to a TIME VALUE in units of DAYS.

So if you enter
[tt]
25:12
[/tt]
Excel assumes you entered 25 HOURS, 12 MINUTES.

However, if you enter...
[tt]
25:12.0
[/tt]
Excel will assume you want 25 MINUTES, 12.0 SECONDS.

Just a little quirk or feature, however you perceive it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GoPens]

Thanks for the information Skip - interesting.