EXCELL --- DISPLAY HOURS MINUTES - QUESTION

[lmcate2]

Would someone tell me how I can type the value of 109 that will represent 1:49

 

[macropod]

Hi,

Try: =109/1440
and format the cell as time.

Cheers

 

[AnotherHiggins]

Without a macro, you can't just type in 109 and have that same cell display 1:49. To display the number of seconds in time as you want, use a formula in another cell to convert for you.

Macropod's formula will work fine. Now this is just personal preference, but I would divide by 86400 instead of 1440. How'd I come up with 86400? That's (60 seconds in a minute * 60 minutes in an hour * 24 hours in a day) = 86400.

So 109/(60*60*24) = 109/86400 = 0.001261574 = 1 minute 49 seconds.

If you type 109 in A1, then in any other cell, you could type in [COLOR=blue white]=A1/86400[/color]

Format the cell containing the formula as either hh:mm:ss or [m]:ss to display the time the way you want. You can format the cell by
[ul][li]Right click[/li]
[li]Choose Format Cells[/li]
[li]Choose Custom from the list on the left[/li]
[li]Type in one of the formats above in the box on the right[/li][/ul]

Still have questions about how the heck Excel deals with times? Then see SkipVought's FAQ entitled Why do Dates and Times seem to be so much trouble?

Good luck!

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[wec43wec]

In response to the original question of entering 109, how would you generate a value of 1:09 by entering 109 in the cell?

 

[GlennUK]

Hi wec43wec .... how is your question "In response to the original question"????????

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

And if you'd read Anotherhiggins post, you would've read that it isn't possible without a macro

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[lmcate2]

Thank you guys, I turned my computer last night and tried different things and I got the solution.

Column A5 will have the value of 109
Column B5 will have the formula =Time(0,A5,0) then I format the cell as hh.mm so I would get 1.49

Sam

 

[SkipVought]

Sam,

Be CAREFUL!

1.40 as formatted time COULD be visually misinterpreted as 1.40 HOURS, which it is not.

1:40 would be better.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]