Formatting times

[bjm]

How do I format a column so that it shows
hours:minutes:seconds:tenths
when either hours or tenths may not be part of the data input?
I'd also prefer not to have zero for the hours or tenths if they're not
there.

And why does the "input field" at the top of the screen show the number as a
time even if I've formatted the cell as one of the other "time" choices?
TIA
bj

 

[Shane Devenshire]

That going to be hard using the Format Cells dialog box but suppose your
times are starting in A1 then the following formula will do what you want:

=TEXT(A1,IF(A1<1/24,"mm:ss.0",IF(ROUND(MOD(A1,1/86400),7)=0,"[h]:mm.ss","[h]:mm:ss.0")))

 

[bjm]

Thanks for the suggestion, I'll give it a try next time I dive into that
project.
bj
(I'm on Usenet, I don't have a yes button)

That going to be hard using the Format Cells dialog box but suppose your
times are starting in A1 then the following formula will do what you want:

=TEXT(A1,IF(A1<1/24,"mm:ss.0",IF(ROUND(MOD(A1,1/86400),7)=0,"[h]:mm.ss","[h]:mm:ss.0")))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

How do I format a column so that it shows
hours:minutes:seconds:tenths
when either hours or tenths may not be part of the data input?
I'd also prefer not to have zero for the hours or tenths if they're not
there.

And why does the "input field" at the top of the screen show the number
as a
time even if I've formatted the cell as one of the other "time" choices?
TIA
bj