Этот вопрос немного старый, но на сегодняшний день, похоже, нет ответа. Я также видел похожие вопросы на ряде сайтов, но пока не нашел ответа, который бы касался только встроенных функций Excel.
Тем не менее, это довольно легко решить с VBA. Вам даже не нужно знать, как программировать, чтобы сделать это, потому что требуемая функция очень проста.
Открыв книгу, просто нажмите Alt+F11 (чтобы открыть редактор VBA). Затем щелкните пункт меню Вставить > Модуль. В новом модуле VBA введите следующее:
Public Function FMT$(ByVal Value, ByVal strFormat)
FMT = VBA.Format$(Value, strFormat)
End Function
Это просто использует собственную функцию Format VBA вместо функции TEXT в Excel. Это именно то, что вы хотите.
Эта новая функция будет форматировать любую дату (или число) в соответствии с любой заданной вами форматной строкой.
Он будет интерпретировать строку формата, используя стандартную (en-US) нотацию, независимо от региональных настроек операционной системы.
Чтобы использовать это в своей книге, просто введите =FMT(A1, "yyyymmdd_hhss") вместо =TEXT(A1, "yyyymmdd_hhss") .
(Конечно, при необходимости вы можете изменить ссылку на ячейку и форматную строку.)
Кстати, вы можете назвать функцию как хотите. Я выбрал FMT потому что он был коротким и потому что функция может форматировать как числа, так и даты. Но вы можете выбрать что-то более описательное, если хотите. Просто не забудьте использовать то же имя в вашем рабочем листе, что и в коде VBA.
Обратите внимание, что строки формата VBA не совсем совпадают со строками пользовательского формата Excel (например, используйте "n" вместо "m" для минут), но они очень похожи. Посмотрите здесь для получения дополнительной информации или поиск MSDN для «Функция форматирования (Visual Basic для приложений)». Прокрутите вниз, чтобы увидеть различные спецификаторы формата даты.
Способ 2
Другой подход, который также использует VBA, но на самом деле может быть проще в обслуживании, заключается в следующем:
- Примените желаемый формат даты к ячейке, используя обычный диалог «Формат ячейки». Эта ячейка может быть на скрытом листе, если вы предпочитаете - она не должна отображаться для конечного пользователя. Предположим, вы применили формат
yyyymmdd\_hhss к ячейке $ A $ 1 (обратите внимание, что подчеркивание должно быть экранировано, как показано на рисунке).
- Добавьте функцию
GetFormat (показанную ниже) к модулю VBA в вашей книге.
- Введите
=GetFormat($A$1, TRUE) в другую ячейку (например, $ B $ 1)
- Эта функция вернет локализованную версию строки формата. Таким образом, даже если вы изначально отформатировали $ A $ 1 с помощью
yyyymmdd\_hhss , когда вы открываете книгу на компьютере, используя французский язык (например), функция покажет aaaammjj\_hhss .
- Теперь просто назовите вторую ячейку во всех ваших функциях
TEXT . Например: =TEXT(F22, $B$1) .
Вот код VBA:
Public Function GetFormat$(Cell As Range, Optional ByVal UseLocal As Boolean)
If UseLocal Then
GetFormat = Cell.NumberFormatLocal
Else
GetFormat = Cell.NumberFormat
End If
End Function
Это позволяет нам "проверять" ячейку, которую вы первоначально отформатировали ($ A $ 1), чтобы получить локализованную строку формата. Эта строка будет представлять тот же формат, который вы применили, но она будет использовать правильные буквы для ТЕКСТА для интерпретации (например, "j" вместо "d"), поэтому отображаемое значение дат будет постоянным во всех локалях.
Если вы хотите использовать только один формат даты для всей книги, вам нужно будет выполнить шаги 1-4 один раз. Затем повторите шаг 5 (функция TEXT) во всех ячейках, где вы в данный момент используете его, но вместо жесткого кодирования формата вы просто ссылаетесь на ячейку, содержащую локализованную строку формата ($ B $ 1 в инструкциях примера) ,
Обратите внимание, что второй аргумент GetFormat указывает функции, возвращать ли локализованную версию (которая зависит от региональных настроек) или "стандартную" версию (которая всегда основана на en-US).
Вот некоторые скриншоты, которые могут сделать это более ясным.
- На рисунке в столбце 1 перечислены несколько представлений одной даты с применением разных форматов.
- Обратите внимание, что строки 2 и 3 используют форматы даты "по умолчанию" в Excel. (Они отмечены звездочкой в диалоговом окне "Формат" и указывают на то, что должен использоваться формат даты по умолчанию). Также обратите внимание, что в строке 5 используется LCID в квадратных скобках, что делает язык, используемый для названий месяцев и дней, английским (разные LCID могут использоваться для указания других языков).
- Во втором столбце показан результат
GetFormat(Cell, FALSE) для каждой ячейки в столбце 1. (Напомним, что FALSE для второго параметра заставляет функцию возвращать не локализованные форматы).
- В третьем столбце показано, что
GetFormat(Cell, TRUE) для каждой ячейки в столбце 2. (т.е. в локализованных форматах).
- В четвертом столбце показан результат функции TEXT с использованием исходного необработанного значения даты и локализованного результата
GetFormat для повторного создания формата, показанного в столбце 1. Однако обратите внимание, что к этому столбцу не применяется форматирование чисел. Значения являются прямым результатом функции TEXT.
Приведенные выше результаты для случая с английским (США) не очень интересны, но вы можете сравнить их со следующими результатами, которые были получены путем изменения региональных настроек моей операционной системы на различные другие локали. Важно отметить, что с помощью GetFormat в сочетании с TEXT мы можем сохранить постоянный результат для числовых форматов (тех, которые не включают названия дня или месяца) во всех локалях. И, ограничивая язык с помощью LCID (как в строке 5), мы можем даже сохранить постоянный формат, включив также названия дней и месяцев.
Этот метод работает для большинства языковых стандартов, однако следует отметить, что сценарии, используемые для представления индусско-арабских чисел, НЕ одинаковы во всех языковых стандартах. Поэтому региональные настройки, такие как настройки в непальском (Индия) регионе, приведут к форматам дат, которые "выглядят" иначе, чем даты в США. Но на самом деле они находятся в одном и том же "формате" с точки зрения позиций чисел - они просто используют разные символы для чисел.
