Sum Time - strange result (site & cpearson researched)

[proverbs]

Hello

Issue with adding time (researched site and cpearson and still can’t
resolve)

Raw data is exported from a database (engine unknown – large multi user
organisation). Export facility has many format options in addition to
Excel.

Excel options range through ‘Excel 2.0’ to ‘Excel 5.0’ and also csv.
Current raw data exports done in both Excel 4.0 & 5.0 and issue with
both.

Example column of times to Sum are shown thus (number of rows varies
but approx 30):

489:55:06
311:33:53
165:26:51

Underlying cell format on export is ‘General’. Cells reformatted to
hh:mm:ss. Cell for Sum function formatted to [h]:mm:ss.

Result – 0:00:00

Manually overtype the data cells – Result 966:55:50 which is correct.

Data cells change on over type so reformatted these to [h]:mm:ss to
maintain

Get the result required but would like to know why I have to manually
re-enter to do so?

Tried formatting sheet in new workbook with [h]:mm:ss and use ‘paste
specia’l, ‘values’ to place raw data into it from the export (rather
than re-formatting within the Excel export) and same issue.

Any thoughts out there?
(Excel used - 2002 SP3. Think its 2003 at work)

 

[Sandy Mann]

It sounds like your *times* are really text. Try this on a Copy of you
data:
Copy an unused cell then highlight the *times* and select Past Special > Add

I got:

20.41326
12.98186
6.893646

with your sample data.

Then reformat the cells as [H]:mm:ss (even if they were that format to start
with)

It worked for me.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[proverbs]

Sandy,

Two things. 1) tomorrow I go to work a happy man and 2) I'll love Perth
forever. Not necessarily in that order!

This worked fine.

Now I have a solution, though to satisfy my own curiosity I shall be
looking for an explanation as why cells formated on export as General
and then re-formatted as [h]:mm:ss continue to remain as *text*.

Sandy, or anyone, any ideas on this? No replies really required as
primary issue resolved.

Thank you for being there for me.

Phill E

It sounds like your *times* are really text. Try this on a Copy of you

data:
Copy an unused cell then highlight the *times* and select Past Special

Add

I got:

20.41326
12.98186
6.893646

with your sample data.

Then reformat the cells as [H]:mm:ss (even if they were that format to
start
with)

It worked for me.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Hello

Issue with adding time (researched site and cpearson and still can't
resolve)

Raw data is exported from a database (engine unknown - large multi user
organisation). Export facility has many format options in addition to
Excel.

Excel options range through 'Excel 2.0' to 'Excel 5.0' and also csv.
Current raw data exports done in both Excel 4.0 & 5.0 and issue with
both.

Example column of times to Sum are shown thus (number of rows varies
but approx 30):

489:55:06
311:33:53
165:26:51

Underlying cell format on export is 'General'. Cells reformatted to
hh:mm:ss. Cell for Sum function formatted to [h]:mm:ss.

Result - 0:00:00

Manually overtype the data cells - Result 966:55:50 which is correct.

Data cells change on over type so reformatted these to [h]:mm:ss to
maintain

Get the result required but would like to know why I have to manually
re-enter to do so?

Tried formatting sheet in new workbook with [h]:mm:ss and use 'paste
specia'l, 'values' to place raw data into it from the export (rather
than re-formatting within the Excel export) and same issue.

Any thoughts out there?
(Excel used - 2002 SP3. Think its 2003 at work)

 

[Roger Govier]

Hi

Changing the format of a cell after data has been entered, does not
change the content of the cell to that format.
Entering new data, or performing an operation upon existing data, will
cause the data to take on the underlying cell format.

When Sandy added nothing to the cell value, an operation was performed,
hence the conversion.

--
Regards

Roger Govier

Sandy,

Two things. 1) tomorrow I go to work a happy man and 2) I'll love
Perth
forever. Not necessarily in that order!

This worked fine.

Now I have a solution, though to satisfy my own curiosity I shall be
looking for an explanation as why cells formated on export as General
and then re-formatted as [h]:mm:ss continue to remain as *text*.

Sandy, or anyone, any ideas on this? No replies really required as
primary issue resolved.

Thank you for being there for me.

Phill E

It sounds like your *times* are really text. Try this on a Copy of
you

data:
Copy an unused cell then highlight the *times* and select Past
Special

Add

I got:

20.41326
12.98186
6.893646

with your sample data.

Then reformat the cells as [H]:mm:ss (even if they were that format
to
start
with)

It worked for me.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Hello

Issue with adding time (researched site and cpearson and still
can't
resolve)

Raw data is exported from a database (engine unknown - large multi user
organisation). Export facility has many format options in addition to
Excel.

Excel options range through 'Excel 2.0' to 'Excel 5.0' and also
csv.
Current raw data exports done in both Excel 4.0 & 5.0 and issue
with
both.

Example column of times to Sum are shown thus (number of rows
varies
but approx 30):

489:55:06
311:33:53
165:26:51

Underlying cell format on export is 'General'. Cells reformatted to
hh:mm:ss. Cell for Sum function formatted to [h]:mm:ss.

Result - 0:00:00

Manually overtype the data cells - Result 966:55:50 which is correct.

Data cells change on over type so reformatted these to [h]:mm:ss to
maintain

Get the result required but would like to know why I have to manually
re-enter to do so?

Tried formatting sheet in new workbook with [h]:mm:ss and use
'paste
specia'l, 'values' to place raw data into it from the export
(rather
than re-formatting within the Excel export) and same issue.

Any thoughts out there?
(Excel used - 2002 SP3. Think its 2003 at work)

 

[Sandy Mann]

Hi Phil,

Adding to what Roger said, and thanks for jumping in Roger - I was away for
the rest of the evening - and forgive me Phil if you already know this:

I initially found it mind boggling that you could use an empty cell but the
reason is of course that Excel values an empty cell as zero. You could
therefore use in another cell the formula: =B1+0 and it would do the same
thing. Excel is clever enough to figure out that if you are performing a
maths operation on a text representation of a number you want to change the
text into a real number. Using a copied empty cell cell just lets you do it
all in the original cell.

The real number that it changes 489:55:06 into is 20.41326 which is 20.41326
*DAYS*

If you enter a text 489 and perform the same actions as I said you will get
11736:00:00 because the 489 is changed into 489 *DAYS* and 489 *DAYS* have
11,736 hours.

Excel is clever enough to know that even 489: (with a colon) is hours and so
will result in 20.375 *Days* and thus when the formatting changes how the
number looks, (but as Roger said, not the underlying number in the cell), it
displays as 489:00:00

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Sandy,

Two things. 1) tomorrow I go to work a happy man and 2) I'll love Perth
forever. Not necessarily in that order!

This worked fine.

Now I have a solution, though to satisfy my own curiosity I shall be
looking for an explanation as why cells formated on export as General
and then re-formatted as [h]:mm:ss continue to remain as *text*.

Sandy, or anyone, any ideas on this? No replies really required as
primary issue resolved.

Thank you for being there for me.

Phill E

It sounds like your *times* are really text. Try this on a Copy of you

data:
Copy an unused cell then highlight the *times* and select Past Special

Add

I got:

20.41326
12.98186
6.893646

with your sample data.

Then reformat the cells as [H]:mm:ss (even if they were that format to
start
with)

It worked for me.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Hello

Issue with adding time (researched site and cpearson and still can't
resolve)

Raw data is exported from a database (engine unknown - large multi user
organisation). Export facility has many format options in addition to
Excel.

Excel options range through 'Excel 2.0' to 'Excel 5.0' and also csv.
Current raw data exports done in both Excel 4.0 & 5.0 and issue with
both.

Example column of times to Sum are shown thus (number of rows varies
but approx 30):

489:55:06
311:33:53
165:26:51

Underlying cell format on export is 'General'. Cells reformatted to
hh:mm:ss. Cell for Sum function formatted to [h]:mm:ss.

Result - 0:00:00

Manually overtype the data cells - Result 966:55:50 which is correct.

Data cells change on over type so reformatted these to [h]:mm:ss to
maintain

Get the result required but would like to know why I have to manually
re-enter to do so?

Tried formatting sheet in new workbook with [h]:mm:ss and use 'paste
specia'l, 'values' to place raw data into it from the export (rather
than re-formatting within the Excel export) and same issue.

Any thoughts out there?
(Excel used - 2002 SP3. Think its 2003 at work)