Using the digits from a time

  • Thread starter Thread starter Paul Barlow
  • Start date Start date
P

Paul Barlow

Hi,

I'm extracting a number from a SQL Server database which represents an
average time. I store this in a cell formatted in Time format e.g.
03:10.00

What I need to do is to be able to read the digits to put into another
cell in number format e.g. 3.1

Haven't been able to find any examples of doing this type of thing so
would appreciate any help, or pointers in the right direction?

Cheers,
Paul
 
A time formatted cell actually contains a "part day". For example, enter .5
in a cell and time format it. You should see 12 Noon in the cell.

So to convert from part day to hours multiply by 24. To convert from part
day to minutes multiply by 24*60. If you put a formula in another cell to
do this, be sure to change its number format to Number, else Excel will use
Time format, which will not give you what you want.
 
Jim Rech said:
A time formatted cell actually contains a "part day". For example, enter .5
in a cell and time format it. You should see 12 Noon in the cell.

So to convert from part day to hours multiply by 24. To convert from part
day to minutes multiply by 24*60. If you put a formula in another cell to
do this, be sure to change its number format to Number, else Excel will use
Time format, which will not give you what you want.
Click to expand...

I think I might have confused you slighty, or i'm reading your reply
wrong?!

Say I have a time formatted cell which contains 0:02:24, I take this
too mean 2 minutes and 24 seconds. At the moment I would manually
type in 2.24 to a number formatted cell, just the way the spreadsheet
I have to use is set up. What I would like if possible is some sort
of vba code, excel funtionality that would allow me to do this
automatically i.e. to strip the digits from the time formatted cell
and rebuild them as a number? Is this possible?

Cheers,
Paul
 
I think I might have confused you slighty, or i'm reading your reply
wrong?!

Say I have a time formatted cell which contains 0:02:24, I take this
too mean 2 minutes and 24 seconds. At the moment I would manually
type in 2.24 to a number formatted cell, just the way the spreadsheet
I have to use is set up. What I would like if possible is some sort
of vba code, excel funtionality that would allow me to do this
automatically i.e. to strip the digits from the time formatted cell
and rebuild them as a number? Is this possible?
Click to expand...

To change excel time into a decimal number, with the portion to the right of
the decimal indicating the number of seconds, then you can use the formula:

=DOLLARFR(Excel_Time*1440,60)

If the DOLLARFR function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.




--ron
 
I'm sure you know that 0:02:24 is 2.4 minutes not 2.24. Still if you want
to select a time formatted cell and run a macro that puts the that time in
your format in the cell to the right, here's an example macro:

Sub Time2Num()
With ActiveCell
.Offset(0, 1).Value = Minute(.Value) & "." & Second(.Value)
End With
End Sub

Adjust this as needed.
 
Back
Top