Solved: Transform general format in time format

[nedy_03]

Hello,

Could u help me please on this simple ( I supouse :banghead: ) question? I have a column with numbers in general format (they indicate the number of seconds of a phone call) and I need to put them in time format (hh:mm:ss). If I do that from he format menu I it apears 00:00:00.

Thx,
Nedy

[Jacob Hilderbrand]

So the seconds are in the cell as a whole number?

Do this:

In an unused cell type 86400
Copy that cell
Select the column / cells your numbers are in
From the Menu Bar: Edit | Paste Special | Divide
From the Menu Bar: Format | Cells | Custom | h:mm:ss

[Bob Phillips]

In an adjacent cel add

=A1/24/60/60

and format as time

[nedy_03]

Thx .. it works in both ways ... A curiosity : why 86400?? :D ... It is the number of seconds of a day, but how it works? if isn't to hard to explain! ...

[Bob Phillips]

86400 is 24*60*60 (the same numbers that I used in my formula). This is because Excel stores time as a faraction ofd a day, 24 hours is 1. There are 24 hours in a day, 60 minutes in an hour, and 60 seconds in a minute.

[nedy_03]

Hi,

There seems to be a problem. For every 60 seconds I get some extra seconds. For example for 60 I abtain 0:01:01, for 120 - 0:02:03, 180 - 0:03:04 ...

Thx,
Nedy


So the seconds are in the cell as a whole number?

Do this:

In an unused cell type 86400
Copy that cell
Select the column / cells your numbers are in
From the Menu Bar: Edit | Paste Special | Divide
From the Menu Bar: Format | Cells | Custom | h:mm:ss

[nedy_03]

This one it works fine ... no extra seconds :P ..



In an adjacent cel add

=A1/24/60/60

and format as time

[Bob Phillips]

It is probably due to Excel's floating point arithmetic engine. There is always the possibility of some small rounding issue being introduced.

[nedy_03]

Hello,

First of all thx for u're answer. Now can u tell me pls how do I do the oposite?? I meen to transform a time format ( 0:01:30) in general format ("90" - that would meen 90 seconds).

[Bob Phillips]

=A1*24*60*60

[gnod]

Hello,

First of all thx for u're answer. Now can u tell me pls how do I do the oposite?? I meen to transform a time format ( 0:01:30) in general format ("90" - that would meen 90 seconds).

just multiply it to 24*60*60 then change the format to "general"

[nedy_03]

Hi there,

I'm back with a new question .. could u please help me? .. This time i sabout access .. I have a combo box with two options .. If I choose one of it I want some fields to be blocked ... if I choose the otherone the same thing for other fields ..

Thx,
Nedy

[Bob Phillips]

Access application? There is a forum for Access who are better at that stuff.