PDA

View Full Version : Solved: Transform general format in time format



nedy_03
12-30-2006, 01:51 AM
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
12-30-2006, 02:03 AM
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
12-30-2006, 03:42 AM
In an adjacent cel add

=A1/24/60/60

and format as time

nedy_03
12-30-2006, 06:31 AM
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
12-30-2006, 07:05 AM
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
01-02-2007, 05:15 AM
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
01-02-2007, 05:20 AM
This one it works fine ... no extra seconds :P ..



In an adjacent cel add

=A1/24/60/60

and format as time

Bob Phillips
01-02-2007, 06:59 AM
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
01-02-2007, 09:44 AM
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
01-02-2007, 09:59 AM
=A1*24*60*60

gnod
01-02-2007, 10:00 AM
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
01-11-2007, 10:18 AM
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
01-11-2007, 10:51 AM
Access application? There is a forum for Access who are better at that stuff.