Time/DateSerial Numeric Value
 

I have this application in which values of time are plugged (i.e.): 10 hours,
52 minutes and 39 seconds (10:52:39 AM). These values are used in
calculations at some point within the worksheet. As it stands Excel does not
allow this to happen easily, since whenever the value is entered it takes on
a time value. I have as examples column E cell E:4=10:52:39 AM, colunm G cell
G:4=2:10:46 AM, column H cell H:4=12:06:02 AM. These three columns are ALL
supposed to be numeric values of 10 hrs, 52 mins,39 sec and 2 hrs,10 mins,46
sec and 12 hrs, 52 mins, 2 seconds.

Is there a method for getting these values to show up and work as numeric
values within their respective columns? If there is, can you supply a work
around or solution by way of a function or line(s) code that will do this?

Appreciate any assistance on this matter. Thanks!

Jay,

The way that I read your question it seem to me that you want to enter a
time ie 10:52:39 AM but have it show in the cell as 10 hours, 52 minutes
and 39 seconds yet still be used in a calculation, I would think that the
answer would be no, a cell cannot hold two different things at the same
time. What I suppose you could do would be to have an event macro which
copied the entered time value to a cell in a hidden column and then build
the text representation of it in the origoinal cell and use the copied time
value in calculations. However, if you then wanted the sheet to alter the
time itself after the calculation then you will be stuck.

It would be a lot simpler to have both forms of the times in adjacent cells.
If say the time is entered in E4 then in F4 have the formula:

=IF(ISNUMBER(E4),HOUR(E4)& " Hours " & MINUTE(E4) & " Minutes " & SECOND(E4)
& " Seconds","")

The font colour in E4 can be set to white, (or the background colour if not
set to 'no fill'), so that the real time in E4 will only show up in the
formula bar when the cell is selected.

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Jay" wrote in message
...
I have this application in which values of time are plugged (i.e.): 10
hours,
52 minutes and 39 seconds (10:52:39 AM). These values are used in
calculations at some point within the worksheet. As it stands Excel does
not
allow this to happen easily, since whenever the value is entered it takes
on
a time value. I have as examples column E cell E:4=10:52:39 AM, colunm G
cell
G:4=2:10:46 AM, column H cell H:4=12:06:02 AM. These three columns are ALL
supposed to be numeric values of 10 hrs, 52 mins,39 sec and 2 hrs,10
mins,46
sec and 12 hrs, 52 mins, 2 seconds.

Is there a method for getting these values to show up and work as numeric
values within their respective columns? If there is, can you supply a work

around or solution by way of a function or line(s) code that will do this?

Appreciate any assistance on this matter. Thanks!

Sorry Sandy, that's not what I want. What's happening is I have a time
duration value of 10 hrs, 52 mins, 39 secs. Thsi is the result of a timediff
between an event start and end. Thus, 10 hrs, 52 mins and 39 secs. When I
enter this value in a cell it does not allow me to list it as a numeric
value, rather it presents it as a time value. I want a numeric value
displayed as 10 hours, 52 mins, 39 secs. Since all of the columns mentioned
are involved in a calculation elsewhere on the worksheet.

Hope this helps explain. If you need more clearity, please post back again!

Thanks for chiming in!


"Sandy Mann" wrote:

Jay,

The way that I read your question it seem to me that you want to enter a
time ie 10:52:39 AM but have it show in the cell as 10 hours, 52 minutes
and 39 seconds yet still be used in a calculation, I would think that the
answer would be no, a cell cannot hold two different things at the same
time. What I suppose you could do would be to have an event macro which
copied the entered time value to a cell in a hidden column and then build
the text representation of it in the origoinal cell and use the copied time
value in calculations. However, if you then wanted the sheet to alter the
time itself after the calculation then you will be stuck.

It would be a lot simpler to have both forms of the times in adjacent cells.
If say the time is entered in E4 then in F4 have the formula:

=IF(ISNUMBER(E4),HOUR(E4)& " Hours " & MINUTE(E4) & " Minutes " & SECOND(E4)
& " Seconds","")

The font colour in E4 can be set to white, (or the background colour if not
set to 'no fill'), so that the real time in E4 will only show up in the
formula bar when the cell is selected.

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Jay" wrote in message
...
I have this application in which values of time are plugged (i.e.): 10
hours,
52 minutes and 39 seconds (10:52:39 AM). These values are used in
calculations at some point within the worksheet. As it stands Excel does
not
allow this to happen easily, since whenever the value is entered it takes
on
a time value. I have as examples column E cell E:4=10:52:39 AM, colunm G
cell
G:4=2:10:46 AM, column H cell H:4=12:06:02 AM. These three columns are ALL
supposed to be numeric values of 10 hrs, 52 mins,39 sec and 2 hrs,10
mins,46
sec and 12 hrs, 52 mins, 2 seconds.

Is there a method for getting these values to show up and work as numeric
values within their respective columns? If there is, can you supply a work

around or solution by way of a function or line(s) code that will do this?

Appreciate any assistance on this matter. Thanks!

Hi!

When I enter this value in a cell it does not allow me to
list it as a numeric value, rather it presents it as a
time value.

That's exactly right, it "presents" it as a time value but
it is in fact a numeric value.

You can do calculations as is. The calculations are done
on the true underlying numeric value.

Enter in a cell, 10:52:39.

Now format that cell as GENERAL. What do you see?

Biff

-----Original Message-----
Sorry Sandy, that's not what I want. What's happening is
I have a time
duration value of 10 hrs, 52 mins, 39 secs. Thsi is the
result of a timediff
between an event start and end. Thus, 10 hrs, 52 mins and
39 secs. When I
enter this value in a cell it does not allow me to list
it as a numeric
value, rather it presents it as a time value. I want a
numeric value
displayed as 10 hours, 52 mins, 39 secs. Since all of the
columns mentioned
are involved in a calculation elsewhere on the worksheet.

Hope this helps explain. If you need more clearity,
please post back again!

Thanks for chiming in!


"Sandy Mann" wrote:

Jay,

The way that I read your question it seem to me that
you want to enter a
time ie 10:52:39 AM but have it show in the cell as 10
hours, 52 minutes
and 39 seconds yet still be used in a calculation, I
would think that the
answer would be no, a cell cannot hold two different
things at the same
time. What I suppose you could do would be to have an
event macro which
copied the entered time value to a cell in a hidden
column and then build
the text representation of it in the origoinal cell and
use the copied time
value in calculations. However, if you then wanted the
sheet to alter the
time itself after the calculation then you will be
stuck.

It would be a lot simpler to have both forms of the
times in adjacent cells.
If say the time is entered in E4 then in F4 have the
formula:

=IF(ISNUMBER(E4),HOUR(E4)& " Hours " & MINUTE(E4) & "
Minutes " & SECOND(E4)
& " Seconds","")

The font colour in E4 can be set to white, (or the
background colour if not
set to 'no fill'), so that the real time in E4 will
only show up in the
formula bar when the cell is selected.

HTH

Sandy

--
to e-mail direct replace @mailinator.com with
@tiscali.co.uk


"Jay" wrote in message
news:595D4EDF-E8EF-4794-9402-
...
I have this application in which values of time are
plugged (i.e.): 10
hours,
52 minutes and 39 seconds (10:52:39 AM). These values
are used in
calculations at some point within the worksheet. As
it stands Excel does
not
allow this to happen easily, since whenever the value
is entered it takes
on
a time value. I have as examples column E cell
E:4=10:52:39 AM, colunm G
cell
G:4=2:10:46 AM, column H cell H:4=12:06:02 AM. These
three columns are ALL
supposed to be numeric values of 10 hrs, 52 mins,39
sec and 2 hrs,10
mins,46
sec and 12 hrs, 52 mins, 2 seconds.

Is there a method for getting these values to show up
and work as numeric
values within their respective columns? If there is,
can you supply a work

around or solution by way of a function or line(s)
code that will do this?

Appreciate any assistance on this matter. Thanks!



.

On Tue, 15 Mar 2005 13:55:05 -0800, Jay wrote:

I have this application in which values of time are plugged (i.e.): 10 hours,
52 minutes and 39 seconds (10:52:39 AM). These values are used in
calculations at some point within the worksheet. As it stands Excel does not
allow this to happen easily, since whenever the value is entered it takes on
a time value. I have as examples column E cell E:4=10:52:39 AM, colunm G cell
G:4=2:10:46 AM, column H cell H:4=12:06:02 AM. These three columns are ALL
supposed to be numeric values of 10 hrs, 52 mins,39 sec and 2 hrs,10 mins,46
sec and 12 hrs, 52 mins, 2 seconds.

Is there a method for getting these values to show up and work as numeric
values within their respective columns? If there is, can you supply a work
around or solution by way of a function or line(s) code that will do this?

Appreciate any assistance on this matter. Thanks!

You need to understand how Excel stores and displays time entries.

If you enter 10:52:39 Excel stores this as a fraction of a day. Actually
0.45322916666667

If it is formatted as any kind of time, in the FORMULA BAR it will show as
10:52:39 AM. I don't believe you can change how it is displayed in the formula
bar, if the cell is formatted as time.

You can change how it is displayed in the CELL, however.
Format/Cells/Number/Time and choose 37:30:55 from the drop down list and it
will display, in the cell as 10:52:39. Or you can use a custom format:
Format/Cells/Number/Custom Type:

[h] "hours" mm" minutes" ss" seconds"

will display, in the cell, as 10 hours 52 minutes 39 seconds


However, the number that is stored there is still 0.45322916666667


If you want to add these numbers, in time format, merely add them

=E4+G4+H4

Just be sure, however you format the result, that the hour portion is in
brackets (e.g. [h]:mm:ss) or else the hours will "roll over" at 24 hours.

The sum of those durations is 25:09:27


--ron