.NumberFormat иногда возвращает неправильное значение с датами и временем

Кажется, что каждую неделю или около того кто-то отправляет вопрос о преобразовании (повреждении?) Дат в американский формат. Как и многие другие, я пытался помочь, но проблема неуловима. Теперь мне интересно, если я обнаружил причину.

Я работаю над приложением, в котором мне нужно извлечь данные из листа Excel и вывести их в виде строк, отформатированных в соответствии со значением, которое может видеть пользователь Excel. Поэтому, если значение «1» отформатировано для отображения как «1,00», тогда я хочу, чтобы строка была «1,00».

Я достигаю этого эффекта, проверяя значение ячейки как число, дату или время. Если это так, я извлекаю числовой формат и использую его для форматирования значения ячейки так:

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

В большинстве случаев это дает мне именно тот результат, который мне нужен. Однако иногда я получаю американские даты и время, когда источник форматируется как британская дата или время.

После долгих экспериментов с Excel 2003 и Excel 2007 я обнаружил причину. (У меня нет доступа к Excel 2010, но из вопросов, которые я выводил, у него та же проблема.) Этот вопрос частично предназначен для того, чтобы раскрыть эту проблему миру, потому что я ничего не могу найти в Интернете, чтобы указать, что кто-то еще заметил это. , (Без сомнения, кто-то ответит, что они погуглили «xyz» и сразу же получили ответ.) Однако основная цель этого вопроса - поиск предложений для получения нужного мне результата во всех ситуациях.

Обычно я ввожу даты, например, «23мар12». Excel распознает это как дату и форматирует как «23-мар-12». Я могу выбратьFormat Cells и введите или выберите пользовательский формат или выберите один из форматов даты, чтобы у меня был любой формат, который я могу себе представить, включая неанглийские имена для дней и месяцев.

Однако в одном случае выбранный формат не является форматом, который записывается: пользовательский формат «дд / мм / гггг» записывается как формат даты «* 14/03/2001». Это, очевидно, не проблема, пока не будет дальше.

Я создал столбец с датами и временем и отформатировал каждый в своем собственном или стандартном формате. Я написал макрос, чтобы извлечь NumberFormat для каждой из этих дат и времени и записать его в виде строки в соседний столбец. Я также отформатировал значение, используя числовой формат, и записал эту строку в третий столбец.

В ряде случаев формат, выбранный и записанный в Excel, не был форматом, возвращаемым 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;@

Значения (1) и (2) в столбце формата Excel были добавлены мной, чтобы указать, что существует два явно идентичных формата. Как видно из столбца NumberFormat, во всех случаях вторая версия подавляет начальный ноль.

Большинство изменений не имеют важного эффекта. «[$ -F800]» и т. Д., По-видимому, являются фиктивными значениями без эффекта. Очевидно, вы можете заменить «F800» кодом страны Microsoft, чтобы названия дней и месяцев были переведены на язык этой страны.

Однако три стандартных формата, которые Microsoft помечает звездочкой, изменены недопустимо. Даты изменяются с прямым порядком байтов на средний; время изменено с 24 часов на 12 часов, а день недели добавлен в «* 14 марта 2001 года».

Звездочка напротив даты ссылается на комментарий: «За исключением предметов, у которых есть звездочка () в списке «Тип» (вкладка «Число», диалоговое окно «Формат ячеек») применяемые форматы даты не меняют порядок дат в операционной системе ». Звездочка в зависимости от времени ссылается на комментарий:« За исключением элементов со звездочкой () в списке «Тип» (вкладка «Число», диалоговое окно «Формат ячеек») применяемые вами форматы времени не меняют порядок времени в операционной системе ».

При необходимости я могу предупредить пользователей о том, что стандартные форматы даты и времени могут не дать желаемого результата. Однако, если они хотят популярный формат «дд / мм / гггг», они не могут его иметь. «Дд-мм-гггг», например, в порядке, но пользовательский формат «дд / мм / гггг» становится форматом даты «* 14/03/2001» становится «м / д / гггг».

Возвращаясь к моей начальной точке: является ли эта странная обработка одного конкретного формата дат причиной, по которой многие люди утверждают, что их даты иногда конвертируются в американский формат, и поэтому проблема настолько неуловима? Я сталкивался с такой проблемой в другом месте одной группы программистов Microsoft, не зная, что делает другая группа. Поэтому некоторые функции всегда работают, а другие - нет? Некоторые программисты Microsoft знают, где искать правильный формат, а другие нет?

Более того, для меня кто-нибудь может предложить:

Как получить точный формат даты или времени?Какой-то другой способ определения выбранного пользователем формата отображения для даты или времени?

КСТАТИ 1: Я помню, что около тридцати лет назад мне сказали, что американские военные не используют формат месяц / день / год; только американские гражданские лица используют этот формат. Кто-нибудь может сказать мне, если это правда?

Кстати 2: похожая проблема с цветами Excel. Excel хранит свои цвета как "ggbbrr", в то время как все остальные держат их как "rrggbb". Программистам для .Net Excel не сообщалось и они не изменяли номер цвета Excel, прежде чем использовать его для управления экраном.