Date problems, is it a bug???

[R-P]

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.

[raypayette]

Not a bug!
Here is the text from Microsoft help:
"Month versus minutes If you use the "m" or "mm" code immediately AFTER the "h" or "hh" code (for hours), or immediately before the "ss" code (for seconds), Excel displays minutes instead of the month."
The enhancements are mine.
There is confusion for the mm that can be interpreted as minutes or months.

[Ron Rosenfeld]

On Fri, 18 Aug 2006 03:10:01 -0700, R-P <[email protected]> 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

[R-P]

Thanks guys. I solved it by just using it as a text-input. I did still try
ss:mm:hh dd/m/yy (didn't work) and MM for months (didn't seem to be allowed).
Guess I'll try mmm or M.
If that doesn't work and I am going to use/calculate with the data I'll use
Labview or so to extract the dates and times.

I'm still having problems understanding the input format vs. the cell
format. If I have entered a cell-format and the input is a textstring fitting
that format, then I really hope that the Windows Regional settings keeps its
claws off my data.....

*: logical: I consider small to big or big to small to be logical (e.g.
ss-mm-hh-dd-MM-yy), anything else, like the American way of writing a month,
then day then year, is so incredibly unlogical....

"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.

[Ron Rosenfeld]

On Fri, 18 Aug 2006 05:26:02 -0700, R-P <[email protected]> wrote:

>Thanks guys. I solved it by just using it as a text-input. I did still try
>ss:mm:hh dd/m/yy (didn't work) and MM for months (didn't seem to be allowed).
>Guess I'll try mmm or M.

mmm or mmmm will display the month; m or M will display the minutes

>If that doesn't work and I am going to use/calculate with the data I'll use
>Labview or so to extract the dates and times.
>
>I'm still having problems understanding the input format vs. the cell
>format. If I have entered a cell-format and the input is a textstring fitting
>that format, then I really hope that the Windows Regional settings keeps its
>claws off my data.....

You're hopes are in vain. As I wrote, the parsing of the input is determined
by the Windows Regional settings.

>
>*: logical: I consider small to big or big to small to be logical (e.g.
>ss-mm-hh-dd-MM-yy), anything else, like the American way of writing a month,
>then day then year, is so incredibly unlogical....

The key to using a program is to know the rules by which it operates. These
may be different from the rules by which you hope it would operate.

As I also wrote, there seems to be a bug in the handling of mm in that, in your
example, it is displaying "minutes" where it should be displaying the month.
The rule seems to be that if a "m" or "mm" follows an "h", even though there
may be intervening characters, it will be interpreted as "minutes" and not as
"month".

This does not apply to mmm or mmmm since they are unambiguous.



--ron