software

How can I format a cell to be quot;hours:minutes:secondsquot; without Excel treating
it as a time of day?

When I enter quot;2:24:38quot; Excel treats it as quot;2:24:38 AMquot;.

My goal is to have Excel treat the times entered as an quot;elapsed timequot; so to
speak. Then, at the bottom of the column, I'm looking to add a total of all
the elapsed times in that column.

How do I accomplish that?

Thanx.

Use this custom format before you enter the time.

[h]:mm:ssquot;Steven Sinclairquot; wrote:

gt; How can I format a cell to be quot;hours:minutes:secondsquot; without Excel treating
gt; it as a time of day?
gt;
gt; When I enter quot;2:24:38quot; Excel treats it as quot;2:24:38 AMquot;.
gt;
gt; My goal is to have Excel treat the times entered as an quot;elapsed timequot; so to
gt; speak. Then, at the bottom of the column, I'm looking to add a total of all
gt; the elapsed times in that column.
gt;
gt; How do I accomplish that?
gt;
gt; Thanx.

You can use the 24 hour format hh:mm:ss

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

quot;Steven Sinclairquot; gt; wrote in
message ...
gt; How can I format a cell to be quot;hours:minutes:secondsquot; without Excel
gt; treating
gt; it as a time of day?
gt;
gt; When I enter quot;2:24:38quot; Excel treats it as quot;2:24:38 AMquot;.
gt;
gt; My goal is to have Excel treat the times entered as an quot;elapsed timequot; so
gt; to
gt; speak. Then, at the bottom of the column, I'm looking to add a total of
gt; all
gt; the elapsed times in that column.
gt;
gt; How do I accomplish that?
gt;
gt; Thanx.Actually Excell treats all times the same. Any time or date entered is
automatically converted to the number of days from 12/31/1899. So you when
you enter 2:24:38, it is converted to 0.0998263888888889 which is equal to
1/0/1900 2:24:38 AM. You can show this by converting the cell to a number.
So to answer your question directly, All time is treated as elapsed time. If
you don't preformat it though, it might look confusing.

For Example:
10:35:24 4:25:12 9:45:32
should equal 24:46:08, but if you don't format it using the format [h]:mm:ss
it will look like 0:46:08

quot;Slothquot; wrote:

gt; Use this custom format before you enter the time.
gt;
gt; [h]:mm:ss
gt;
gt;
gt; quot;Steven Sinclairquot; wrote:
gt;
gt; gt; How can I format a cell to be quot;hours:minutes:secondsquot; without Excel treating
gt; gt; it as a time of day?
gt; gt;
gt; gt; When I enter quot;2:24:38quot; Excel treats it as quot;2:24:38 AMquot;.
gt; gt;
gt; gt; My goal is to have Excel treat the times entered as an quot;elapsed timequot; so to
gt; gt; speak. Then, at the bottom of the column, I'm looking to add a total of all
gt; gt; the elapsed times in that column.
gt; gt;
gt; gt; How do I accomplish that?
gt; gt;
gt; gt; Thanx.

Regarding the answers to my question...I'm thinking the question is not being
understood.

I don't want to enter an actual time ( 10:22:38 AM ).

I'm inputing elapsed times from a printout in the form of ( hh:mm:ss ), not
actual times of day. So, I just need to be able to tell Excel not to format
the time entered as a time of day.

Can that be done?

Thanx.

Okay...I have figured out part of it.

I have a column of start times (E), a column of stop times (F), and a column
of elapsed times (G). For the formula in the cells for column (G), I have
placed the following:

=TEXT(F3-E3,quot;hh:mm:ssquot;)

This properly gives me the elapsed time between columns (E) and (F).

Now, at the bottom of column (G), I'd like to be able to have a total of all
the elapsed times, but can't figure out that formula.

Any ideas?

Thanx.

I don't think you understand how Excel stores time. If you did, you would
realise there is no reason to ask what you are asking. You HAVE to insert
time as a specific time, but you can use the custom format I gave you to
DISPLAY it as elapsed time. Below is from the help under quot;How Excel Stores
Dates and Timesquot;.

How Microsoft Excel stores dates and times
Microsoft Excel stores dates as sequential numbers known as serial values.
Excel stores times as decimal fractions because time is considered a portion
of a day. Dates and times are values and, therefore, can be added,
subtracted, and included in other calculations. For example, to determine the
difference between two dates, you can subtract one date from the other. You
can view a date as a serial value and a time as a decimal fraction by
changing the format of the cell that contains the date or time to General
format.

quot;Steven Sinclairquot; wrote:

gt; Regarding the answers to my question...I'm thinking the question is not being
gt; understood.
gt;
gt; I don't want to enter an actual time ( 10:22:38 AM ).
gt;
gt; I'm inputing elapsed times from a printout in the form of ( hh:mm:ss ), not
gt; actual times of day. So, I just need to be able to tell Excel not to format
gt; the time entered as a time of day.
gt;
gt; Can that be done?
gt;
gt; Thanx.

Don't use the TEXT function. Simply subtract the two times and use a custom
number format.

How to Create a custom number format:
1. Select the cells you want to format.
2. On the Format menu, click Cells, and then click the Number tab.
3. In the Category list, click Custom.
4. In the Type box, insert this...
[h]:mm:ss
5. Click Okay

To SUM the elapsed times use the SUM function and then use the same custom
number format.

quot;Steven Sinclairquot; wrote:

gt; Okay...I have figured out part of it.
gt;
gt; I have a column of start times (E), a column of stop times (F), and a column
gt; of elapsed times (G). For the formula in the cells for column (G), I have
gt; placed the following:
gt;
gt; =TEXT(F3-E3,quot;hh:mm:ssquot;)
gt;
gt; This properly gives me the elapsed time between columns (E) and (F).
gt;
gt; Now, at the bottom of column (G), I'd like to be able to have a total of all
gt; the elapsed times, but can't figure out that formula.
gt;
gt; Any ideas?
gt;
gt; Thanx.

When summing time you want to use the format that was shown as
[h]:mm:ss
the square brackets around h or hh keep the hours from overflowing
into days; otherwise you would not see anything higher than 23 in that posiiton..

Dates are recorded as days past a reference date such as Dec 31, 1899,
and time is stored as fractional days. For more information see
. www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;Slothquot; gt; wrote in message ...
gt; Don't use the TEXT function. Simply subtract the two times and use a custom
gt; number format.
gt;
gt; How to Create a custom number format:
gt; 1. Select the cells you want to format.
gt; 2. On the Format menu, click Cells, and then click the Number tab.
gt; 3. In the Category list, click Custom.
gt; 4. In the Type box, insert this...
gt; [h]:mm:ss
gt; 5. Click Okay
gt;
gt; To SUM the elapsed times use the SUM function and then use the same custom
gt; number format.
gt;
gt; quot;Steven Sinclairquot; wrote:
gt;
gt; gt; Okay...I have figured out part of it.
gt; gt;
gt; gt; I have a column of start times (E), a column of stop times (F), and a column
gt; gt; of elapsed times (G). For the formula in the cells for column (G), I have
gt; gt; placed the following:
gt; gt;
gt; gt; =TEXT(F3-E3,quot;hh:mm:ssquot;)
gt; gt;
gt; gt; This properly gives me the elapsed time between columns (E) and (F).
gt; gt;
gt; gt; Now, at the bottom of column (G), I'd like to be able to have a total of all
gt; gt; the elapsed times, but can't figure out that formula.
gt; gt;
gt; gt; Any ideas?
gt; gt;
gt; gt; Thanx.