On Fri, 18 Aug 2006 03:10:01 -0700, R-P wrote:

Gathered some info from datafiles, not very interesting, but they contain a
time-date that I want to copy.

Format: "ss:mm:hh dd/mm/yy"
So I select the entire column, right-click, select "Format Cells" and go to
the number tab and select 'custom'.

There I type a new 'Type' in the form of, you guessed it, "ss:mm:hh dd/mm/yy"

Then I copy
"00:00:10 20/04/06"
and it comes out looking like this:
"00:00:10 20/04/06"

Then I copy
"01:00:10 09/08/06"
and it comes out looking like this:
"10:00:01 08/00/06"

Please, please shoot me because this is exactly the kind of thing that I
encounter more often and that makes me bounce off the walls. This really
really REALLY sets me off and makes me want to start to throw stuff through
windows. All values above are actually copied from the data-file (opened in
Ultra-Edit) and the excel-file respectively.

I believe there are two problems with what you are doing. It is likely that
you may not be aware that the manner in which Excel interprets date inputs
depends on the Windows Regional settings in the Control Panel. The cell format
only determines how the data is displayed.

In my case, I have the US regional settings, with the short date format being
M/d/yyyy and the time format being h:mm:ss.tt

With your two inputs above:

00:00:10 20/04/06

This input will be interpreted as a text string. When Excel tries to parse the
date portion, it sees a 20 in the month position. Since there is no 20th
month, it assumes the string is not a valid date-time stamp and merely places
it in the cell as a text string. So no matter what date format you select for
that cell, it will still display the same text string of "00:00:10 20/04/06"

In your second example:

01:00:10 09/08/06

Excel interprets this as "08 Sep 2006 1:00:10 AM"

However, when I format this with your custom format "ss:mm:hh dd/mm/yy", I see
displayed "10:00:01 08/00/06"

Note that the time is being displayed properly, although not in the manner
which you might expect because of the way Excel interpreted the data.

However, there also seems to be a bug in the interpretation of the "mm" symbol
within the date string. It is being interpreted as "minute" rather than as
"month" and that is clearly not in accord with the documentation.


According to the documentation for the format code "mm"

mm Display the minute as a number with a leading zero
when appropriate. The m or mm must appear immediately
after the h or hh symbol, or Excel displays the
month rather than the minute.

However, in your custom format, the second "mm" does not appear "immediately"
after the "h or hh" symbol so Excel should be displaying the month. Instead,
it is displaying the minute.


--ron