Change Tme Format to Text

[jtd353]

The values in the cell are formatted as time and using a custom format doesn't produce the desired result. For example, the value of 43:36:38 in reality is 43 hours 36 minutes and 38 seconds but is formatted to display as 7:36:38 PM. Using =Text(L2,"hh:mm:ss") displays 43:36:38 but that still translates to 7:36:38 PM and I need it to be 43 hours, etc. If the hours are under 24 it works perfectly, how do you format it if the number of hours is greater than 24.

 

[SkipVought]

Hi,

I would highly recommend against a text time value.

Use this custom FORMAT instead...
[tt]
[h]:mm:ss
[/tt]
faq68-5827

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Always store your times AS TIMES!!!

Just reformat the cell from hh:mm:ss to [hh]:mm:ss

The square brackets tell Excel to display the total number of hours, even when greater than 24.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Good advise, John. Just a tad LATE! ;-)

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

I've said it before, and I'm sure I'll say it again.

Damn your nimble fingers!


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GSCaupling]

"29 Apr 09 15:46" seems pretty specific, but I guess we need the TT gods to list seconds, too!

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[jtd353]

Thank you all for your suggestions. It now works! The file I'm using is exported from an application and was created by the vendor who formatted the cells as time rather than a number. By inserting a new column and using =TEXT(A1,"[hh]:mm:ss") I was able to get the results I need. I'm using the file as the datasource for a Crystal Report which is why I need the exact value that's in the cell.

 

[SkipVought]

... time rather than a number

You must NOT have read or understood the FAQ I posted.

Time IS a number!!!

Text is NOT TIME!!!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

AS Skip points out, you should read faq68-5827.

This bit - "...formatted the cells as time rather than a number..." - doesn't make any sense.

Just apply the [highlight][red][[/red][/highlight]hh[highlight][red]][/red][/highlight]:mm:ss format to the cell(s) that contains the number/time. Using a formula to convert the time into a text string only serves to cripple the spreadsheet's functionality.

Please understand that we're not picking on you - you came here asking for help and Skip and I are really trying to help you.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.