Clever Geek Handbook

.NumberFormat sometimes returns wrong value with dates and times

It seems like every week or so someone is asking a question about convertible dates (messed up?) To US format. Like many others, I tried to help, but the problem is elusive. Now I wonder if I haven't found the reason.

I am working on an application where I need to extract data from an Excel sheet and output it as strings, formatted according to the value that the Excel user can see. So if the value "1" is formatted to display as "1.00" then I want the string to be "1.00".

I achieve this effect by checking the cell value as a number, date or time. If so, I get the number format and use it to format the cell value like so:

With .Cells(Row, Column) 
  Output string = Format(.Value, .NumberFormat)
End With

In most cases, this gives me exactly the result I want. However, sometimes I get American dates and times when the source is formatted as a UK date or time.

After a lot of experimenting with Excel 2003 and Excel 2007, I discovered the cause. (I don't have access to Excel 2010, but from the questions I have been outputting they have the same problem). This question is partly intended to expose this problem to the world, because I can't find anything on the internet to indicate that someone else noticed this. (No doubt someone will answer that they googled "xyz "and immediately received an answer). However, the main purpose of this question is to look for suggestions to get the result I want in all situations.

I usually enter dates, for example "23mar12". Excel recognizes this as a date and formats it as "23-Mar-12". I can choose Format Cells

and enter or choose my own format, or choose one of the date formats so that I can have any format I can imagine, including non-English names for days and months.

However, in one case, the format I choose is not the format that is being written: The custom format "dd / mm / yyyy" is written as the date format "* 14/03/2001". This is clearly not a problem until it goes below the line.

I created a column of dates and times and formatted each one with a different normal or standard format. I wrote a macro to extract the NumberFormat for each of these dates and times and write it as a string to an adjacent column. I also formatted the value using number format and wrote this line in the third column.

In a number of cases, the format selected and written by Excel was not the format returned by NumberFormat:

Excel format            NumberFormat
Date: * 14/03/2001       m/d/yyyy
Date: * 14 March 2001   [$-F800]dddd, mmmm dd, yyyy
Date: 14/03/2001        dd/mm/yyyy;@
Date: 14/03/01          dd/mm/yy;@
Date: 14/3/01           d/m/yy;@
Date: 14.3.01           d.m.yy;@
Date: 2001-03-14        yyyy-mm-dd;@
Date: 14 March 2001 (1) [$-809]dd mmmm yyyy;@
Date: 14 March 2001 (2) [$-809]d mmmm yyyy;@
Custom: hh:mm:ss        h:mm:ss
Time: * 13:30:55        [$-F400]h:mm:ss AM/PM
Time: 13:30:55 (1)      hh:mm:ss;@
Time: 13:30:55 (2)      h:mm:ss;@
Time: 01:30:55 PM       [$-409]hh:mm:ss AM/PM;@
Time: 1:30:55 PM        [$-409]h:mm:ss AM/PM;@

The values ​​(1) and (2) in the Excel format column were added by me to indicate that there are two apparently identical formats. As you can see from the NumberFormat column, the second version suppresses leading zero in each case.

Most of the changes have no significant effect. "[$ -F800]" etc., appear to be dummy values ​​with no effect. Obviously, you can replace "F800" with the Microsoft country code so that the day and month names are translated into that country.

However, the three standard formats that Microsoft marks with an asterisk have changed unacceptably. Dates range from small end to medium. the time changes from 24 hours to 12 hours, and the day of the week is added to "* March 14, 2001".

An asterisk with dates refers to a comment: "Except for items with an asterisk () in the Type list (Number tab, Format Cells), the date formats applied do not change dates with system operations." An asterisk over time refers to the comment: "With the exception of items with an asterisk () in the Type list (Number tab, Format Cells), the applied temporary formats do not switch temporary orders with the operating system."

If I need to, I can warn my users that standard date and time formats may not work. However, if they want the popular "dd / mm / yyyy" format, they cannot get it. "dd-mm-yyyy" for example is ok, but the custom format "dd / mm / yyyy" becomes the date format "* 14/03/2001" becomes "m / d / yyyy".

Coming back to my discovery: is this a weird handling of one particular date format, which is why many people claim that their dates are sometimes converted to US format and is this why the problem is so elusive? I ran into this problem elsewhere in one group of Microsoft programmers without knowing what the other group was doing. Is that why some functions always work and others sometimes don't? Some Microsoft programmers know where to look for the correct format, while others don't?

More importantly, for me, anyone can suggest:

  • How do I get the true date or time format?
  • Another way to define the user-selected date or time display format?

By the way: I remember thirty years ago I was told that the US military does not use the month / day / year format; only US civilians use this format. Can anyone tell me if this is true?

BTW 2: Similar issue with Excel colors. Excel stores its colors as "ggbbrr" and everyone else keeps them as "rrggbb". The programmers for the .NET interpolation did not tell Excel or change the Excel color number before using it to control the screen.

+3


source to share


3 answers


Basically I am running into formatting and date issues when opening text files that have been saved with different locales. Two useful properties of the cell for this:

  • .Text

    returns the value of the cell when it is displayed
  • .Value2

    returns the unformatted cell value or serial number of the date.


As you say, the standard date and number formats depend on the regional settings of Windows, and this may be undesirable behavior as the same workbook may appear differently in different regions. MS has introduced regional code prefixes in number formats (around Excel 2000?) Which provide consistent display when needed, but must be explicitly selected.

If you really want to see the date or number when the user entered it, you can extract the contents of the .xlsx file by looking at the sheet cell format and the generic xml definitions of strings that list the number formats in the saved workbook.I really don't see the need to do this because that the main value is stored internally as a serial number and that won't change.

+1


source


BTW 1: It's been almost 30 years since I was in the army ... I worked in helicopters and I was taught to use a format like this in airplane magazines: April 3rd. So how do I still write dates. So, is it not surprising that 3/4/2012 is April 3rd or March 4th?



0


source


I hacked it up: I am rewriting the original data in a known format. it relies on DateSerial and TimeSerial:

'Google spreadsheet stores dates in USA format (MM/DD/YYYY).  We're in Australia, using DD/MM/YYYY, so we need to swap them.
                    '
                        With dc 'the cell who contains a date in USA format.
                             d = .Value      'capture value in USA format
                             t = TimeValue(d)
                             .NumberFormat = "dd/mm/yyyy" 'set to OZ format, so Excel knows the values were swapped in its internal math.
                             .Value = DateSerial(Year(d), Month(d), Day(d)) 'DateSerial takes y,d,m. We swap Month and Day components, to get OZ format dates
                             .Value = .Value + TimeSerial(Hour(t), Minute(t), Second(t))
                             dc.Font.Bold = True                         ' We bold the cells that are swapped, for debugging
                         End With
                    End If
0


source






More articles:


All Articles