VBA Excel. Местное время для любого города

Как получить точное местное время для любого города мира с помощью VBA Excel?. Использовать бесплатное API, возвращающее точное время в формате JSON.

Содержание

Актуальность и способ получения местного времени

Если нужно отслеживать текущее время в разных городах мира — например, для планирования международных звонков, работы с удалёнными командами, для организации командировок или просто из любопытства — стандартные средства Excel не помогут.

Однако, с помощью небольшого кода VBA и бесплатного интернет-сервиса (https://timeapi.io), можно получать актуальное время для любого города с учётом часового пояса и перехода на летнее время (Daylight Saving Time).

С помощью этой статьи вы сможете создать готовое решение для получения текущей даты и местного времени, которое включает:

список городов с их IANA-идентификаторами часовых поясов;
выпадающий список для выбора города;
пользовательскую функцию VBA, которая обращается к открытому API и возвращает текущую дату и/или время;
вспомогательную функцию VBA, которая извлекает и возвращает значение строкового ключа из JSON, в нашем случае — актуальную дату и время;
настройку автоматического обновления времени при любом изменении данных или по запросу.

Всё это работает в обычном приложении Excel (Windows) без установки дополнительных программ.

Что такое IANA-идентификатор часового пояса?

IANA Time Zone Database (или tz database) — это общепризнанная база данных часовых поясов, которую используют операционные системы, языки программирования и интернет-сервисы.

IANA (Internet Assigned Numbers Authority) переводится, как «Управление по присвоению номеров в Интернете».

Вместо IANA-идентификатор часового пояса (IANA Time Zone ID) часто используют более короткое выражение — TZ-идентификатор (tz database ID), где TZ — это Time Zone (часовой пояс).

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

Europe/Moscow
Asia/Tokyo
America/New_York

TZ-идентификаторы IANA учитывают не только часовые пояса, но и все исторические и текущие правила перехода на летнее время, поэтому сведения о дате и времени, полученные с их помощью, всегда надёжны и актуальны.

Подготовка таблицы со списком городов

Откройте новую книгу Excel и переименуйте два листа. Назовите один — «Список городов», а второй — «Время в городе».

Скопируйте таблицу ниже и вставьте её на лист «Список городов». Перед вставкой увеличьте с запасом ширину столбцов A и B.

Город	TZ-идентификатор (IANA)
Анкара, Турция	Europe/Istanbul
Астана, Казахстан	Asia/Almaty
Багдад, Ирак	Asia/Baghdad
Бангалор, Индия	Asia/Kolkata
Бангкок, Таиланд	Asia/Bangkok
Берлин, Германия	Europe/Berlin
Бразилиа, Бразилия	America/Sao_Paulo
Буэнос-Айрес, Аргентина	America/Argentina/Buenos_Aires
Варшава, Польша	Europe/Warsaw
Владивосток, Россия	Asia/Vladivostok
Гуанчжоу, Китай	Asia/Shanghai
Дакка, Бангладеш	Asia/Dhaka
Дели, Индия	Asia/Kolkata
Джакарта, Индонезия	Asia/Jakarta
Казань, Россия	Europe/Moscow
Каир, Египет	Africa/Cairo
Канберра, Австралия	Australia/Sydney
Карачи, Пакистан	Asia/Karachi
Киншаса, ДР Конго	Africa/Kinshasa
Лагос, Нигерия	Africa/Lagos
Лахор, Пакистан	Asia/Karachi
Лондон, Великобритания	Europe/London
Лос-Анджелес, США	America/Los_Angeles
Мадрид, Испания	Europe/Madrid
Мехико, Мексика	America/Mexico_City
Минск, Беларусь	Europe/Minsk
Москва, Россия	Europe/Moscow
Мумбаи, Индия	Asia/Kolkata
Новосибирск, Россия	Asia/Novosibirsk
Нью-Йорк, США	America/New_York
Осака, Япония	Asia/Tokyo
Оттава, Канада	America/Toronto
Париж, Франция	Europe/Paris
Пекин, Китай	Asia/Shanghai
Рим, Италия	Europe/Rome
Самара, Россия	Europe/Samara
Санкт-Петербург, Россия	Europe/Moscow
Сан-Паулу, Бразилия	America/Sao_Paulo
Сеул, Южная Корея	Asia/Seoul
Стамбул, Турция	Europe/Istanbul
Ташкент, Узбекистан	Asia/Tashkent
Тегеран, Иран	Asia/Tehran
Токио, Япония	Asia/Tokyo
Тяньцзинь, Китай	Asia/Shanghai
Ханой, Вьетнам	Asia/Ho_Chi_Minh
Хайдарабад, Индия	Asia/Kolkata
Ченнаи, Индия	Asia/Kolkata
Чунцин, Китай	Asia/Chongqing
Шанхай, Китай	Asia/Shanghai
Эр-Рияд, Саудовская Аравия	Asia/Riyadh

Чтобы работать с данными было удобнее, преобразуйте вставленный диапазон в умную таблицу. Выделите данные вместе с заголовками, нажмите Ctrl+T (или через меню Вставка -> Таблица) и нажмите OK. Если хотите переименовать таблицу, перейдите на вкладку Конструктор таблиц и введите новое имя. Я оставил имя по умолчанию — Таблица1.

Теперь вы можете ссылаться на столбцы по именам, а при добавлении новых городов таблица будет автоматически расширяться.

Если вы не найдёте нужных городов и TZ-идентификаторов в представленной таблице, вы можете скопировать новые по ссылке IANA Time Zone Database и добавить в таблицу, а также удалить лишние.

Пример получившейся таблицы на листе «Список городов»:

Таблица со списком городов и TZ-идентификаторов

Создание выпадающего списка для выбора города

Подготовка рабочего листа

Перейдите на лист «Время в городе» и заполните строку заголовков названиями столбцов:

Город
TZ-идентификатор
Дата
Время

Заголовки можно выделить жирным шрифтом и применить горизонтальное выравнивание по центру.

Именованный диапазон для списка

Создание именованного диапазона для раскрывающегося списка:

Откройте вкладку «Формулы» → «Диспетчер имён» (или нажмите Ctrl+F3).
Нажмите «Создать».
В поле «Имя» введите СписокГородов.
В поле «Диапазон» введите формулу: =Таблица1[Город]
(это динамическая ссылка на столбец «Город» в умной таблице)
Нажмите ОК.

Добавление выпадающего списка

Теперь создадим сам раскрывающийся список:

Выделите ячейку A2 на листе «Время в городе».
Перейдите на вкладку «Данные» → «Проверка данных».
В списке «Тип данных» выберите «Список».
В поле «Источник» введите: =СписокГородов.
Нажмите ОК.

Теперь в ячейке A2 появится выпадающий список со всеми городами из таблицы.

Примечание: в поле «Источник» можно было бы сразу ввести =Таблица1[Город] без создания дополнительного именованного диапазона СписокГородов, но это не всегда работает.

Получение TZ-идентификатора через ВПР

Рядом с выбранным городом надо автоматически подтянуть его часовой пояс из умной таблицы. В ячейку B2 введите формулу:

=ВПР(A2;Таблица1;2;ЛОЖЬ)

Эта формула ищет город из A2 в первом столбце таблицы Таблица1 и возвращает значение из второго столбца — TZ-идентификатор.

Теперь у нас есть и город, и его часовой пояс.

Пользовательская функция «МестноеВремя»

Самая важная часть получения местного времени — это функция VBA Excel, которая отправляет запрос к интернет-сервису и получает текущее время в заданном часовом поясе.

Функция МестноеВремя использует бесплатное API: https://timeapi.io. Оно возвращает точное время в формате JSON без ключей и ограничений.

Открываем редактор VBA

Нажмите Alt+F11, чтобы открыть редактор Visual Basic for Applications. В меню выберите Insert → Module. В появившийся чистый модуль вставьте приведённый ниже код.

Важно: функция обязательно должна находиться в обычном (стандартном) модуле, а не в модуле листа или книги.

Код функции «МестноеВремя»

Полный код пользовательской функции для получения местного времени в любом городе мира по IANA-идентификатору часового пояса:

Public Function МестноеВремя(timeZone As String, Optional frmt As Byte) As String

    ' Помечаем функцию как изменяемую, чтобы Excel обновлял её значение при каждом пересчете листа
    Application.Volatile True
    
    Dim http As Object
    Dim url As String
    Dim response As String
    Dim dt As String
    Dim uniqueParam As String
    
    ' Добавляем уникальный параметр с текущей датой и временем,
    ' чтобы обойти кэширование запросов на стороне Excel
    uniqueParam = "&_=" & Format(Now, "yyyymmddhhmmss")
    
    ' Формируем URL. EncodeURL кодирует пробелы и спецсимволы в TZ-идентификаторе
    url = "https://timeapi.io/api/time/current/zone?timeZone=" & _
        Application.WorksheetFunction.EncodeURL(timeZone) & uniqueParam
    
    On Error GoTo ErrHandler
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    ' Добавляем заголовок, чтобы сервер точно вернул JSON
    http.setRequestHeader "Accept", "application/json"
    http.send
    
    If http.Status = 200 Then
        response = http.responseText
        dt = ExtractJsonValue(response, "dateTime")
        If dt <> "" Then
            ' Формат ответа: 2024-04-25T15:30:10.123
            ' Убираем миллисекунды и заменяем T на пробел
            dt = Replace(dt, "T", " ")
            If InStr(dt, ".") > 0 Then dt = Left(dt, InStr(dt, ".") - 1)
            ' Убираем Z в конце, если есть
            If Right(dt, 1) = "Z" Then dt = Left(dt, Len(dt) - 1)
            
                ' В зависимости от параметра frmt возвращаем дату, время или полную строку
                If frmt = 1 Then
                    МестноеВремя = Left(dt, 10)     ' дата YYYY-MM-DD
                ElseIf frmt = 2 Then
                    МестноеВремя = Right(dt, 8)     ' время HH:MM:SS
                Else
                    МестноеВремя = dt               ' дата + время
                End If
        Else
            МестноеВремя = "Ошибка: поле dateTime не найдено. Ответ: " & Left(response, 200)
        End If
    Else
        МестноеВремя = "Ошибка HTTP: " & http.Status & " - " & http.statusText
    End If
    Exit Function
    
ErrHandler:
    МестноеВремя = "Ошибка сети: " & Err.Description
End Function

Private Function ExtractJsonValue(json As String, key As String) As String
    Dim keyPos As Long
    Dim colonPos As Long
    Dim startQuote As Long
    Dim endQuote As Long
    
    ' Извлекаем значение ключа из JSON
    
    keyPos = InStr(1, json, """" & key & """", vbTextCompare)
    If keyPos = 0 Then Exit Function
    
    colonPos = InStr(keyPos + Len(key) + 2, json, ":")
    If colonPos = 0 Then Exit Function
    
    startQuote = InStr(colonPos + 1, json, """")
    If startQuote = 0 Then Exit Function
    
    endQuote = InStr(startQuote + 1, json, """")
    If endQuote = 0 Then Exit Function
    
    ExtractJsonValue = Mid(json, startQuote + 1, endQuote - startQuote - 1)
End Function

Public Function МестноеВремя(timeZone As String, Optional frmt As Byte) As String

' Помечаем функцию как изменяемую, чтобы Excel обновлял её значение при каждом пересчете листа

Application.Volatile True

Dim http As Object

Dim url As String

Dim response As String

Dim dt As String

Dim uniqueParam As String

' Добавляем уникальный параметр с текущей датой и временем,

' чтобы обойти кэширование запросов на стороне Excel

uniqueParam = "&_=" & Format(Now, "yyyymmddhhmmss")

' Формируем URL. EncodeURL кодирует пробелы и спецсимволы в TZ-идентификаторе

url = "https://timeapi.io/api/time/current/zone?timeZone=" & _

Application.WorksheetFunction.EncodeURL(timeZone) & uniqueParam

On Error GoTo ErrHandler

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", url, False

' Добавляем заголовок, чтобы сервер точно вернул JSON

http.setRequestHeader "Accept", "application/json"

http.send

If http.Status = 200 Then

response = http.responseText

dt = ExtractJsonValue(response, "dateTime")

If dt <> "" Then

' Формат ответа: 2024-04-25T15:30:10.123

' Убираем миллисекунды и заменяем T на пробел

dt = Replace(dt, "T", " ")

If InStr(dt, ".") > 0 Then dt = Left(dt, InStr(dt, ".") - 1)

' Убираем Z в конце, если есть

If Right(dt, 1) = "Z" Then dt = Left(dt, Len(dt) - 1)

' В зависимости от параметра frmt возвращаем дату, время или полную строку

If frmt = 1 Then

МестноеВремя = Left(dt, 10) ' дата YYYY-MM-DD

ElseIf frmt = 2 Then

МестноеВремя = Right(dt, 8) ' время HH:MM:SS

Else

МестноеВремя = dt ' дата + время

End If

Else

МестноеВремя = "Ошибка: поле dateTime не найдено. Ответ: " & Left(response, 200)

End If

Else

МестноеВремя = "Ошибка HTTP: " & http.Status & " - " & http.statusText

End If

Exit Function

ErrHandler:

МестноеВремя = "Ошибка сети: " & Err.Description

End Function

Private Function ExtractJsonValue(json As String, key As String) As String

Dim keyPos As Long

Dim colonPos As Long

Dim startQuote As Long

Dim endQuote As Long

' Извлекаем значение ключа из JSON

keyPos = InStr(1, json, """" & key & """", vbTextCompare)

If keyPos = 0 Then Exit Function

colonPos = InStr(keyPos + Len(key) + 2, json, ":")

If colonPos = 0 Then Exit Function

startQuote = InStr(colonPos + 1, json, """")

If startQuote = 0 Then Exit Function

endQuote = InStr(startQuote + 1, json, """")

If endQuote = 0 Then Exit Function

ExtractJsonValue = Mid(json, startQuote + 1, endQuote - startQuote - 1)

End Function

Пояснения к коду функции

Application.Volatile True — заставляет Excel пересчитывать функцию при каждом вычислении листа (F9, открытие книги, изменение любой ячейки). Это необходимо, чтобы время обновлялось даже при повторном выборе того же города.
uniqueParam — добавляет к URL изменяющуюся строку типа &_=20260426143022. Без этого параметра MSXML2.XMLHTTP может кэшировать ответ, и вы будете видеть старое время. Теперь каждый запрос уникален.
Application.WorksheetFunction.EncodeURL — функция, появившаяся в Excel с 2013 года, корректно кодирует пробелы и другие символы в идентификаторе. Если ваша версия Excel не поддерживает эту функцию, просто удалите её (оставив только timeZone), так как в нашей таблице нет TZ-идентификаторов с пробелами, а слэши обрабатываются браузерами корректно.
ExtractJsonValue — упрощённый парсер JSON. Он ищет поле "dateTime" в ответе сервера и возвращает его значение.

Функция имеет один обязательный аргумент (timeZone) и один необязательный (frmt):

=GetWorldTime("Europe/Moscow") → вернёт дату и время 2026-04-26 13:30:22
=GetWorldTime("Europe/Moscow"; 1) → вернёт только дату 2026-04-26
=GetWorldTime("Europe/Moscow"; 2) → вернёт только время 13:30:22

Итоговые формулы на листе «Время в городе»

Вернёмся на лист «Время в городе»:

В ячейке A2 у нас выпадающий список с городами.
В ячейке B2 формула =ВПР(A2;Таблица1;2;ЛОЖЬ) — она подставляет TZ-идентификатор.
В ячейке C2 мы хотим получить текущую дату в выбранном городе. Для этого пишем формулу: =МестноеВремя(B2;1)
В ячейке D2 надо отобразить текущее время в выбранном городе. Для этого вставляем формулу: =МестноеВремя(B2;2)

Теперь вы можете выделить диапазон A2:D2 и протянуть его, копируя, вниз. Не копируйте на большое количество строк, так как при обновлении формул будет много запросов на сервер, и он может временно ограничить доступ.

Пример получившейся таблицы на листе «Время в городе»:

Получение местного времени для выбранного города

Теперь при выборе в первом столбце других городов из выпадающего списка, дата и время в ячейках третьего и четвёртого столбцов будут автоматически обновляться.

Автоматическое обновление даты и времени

Поскольку функция МестноеВремя помечена как Volatile, Excel будет пересчитывать её при:

нажатии клавиши F9 (пересчёт всех формул);
изменении любой ячейки на любом листе;
открытии книги;
вставке новой строки или столбца.

Обратите внимание: при очень частых запросах (раз в несколько секунд) сервис может временно ограничить доступ. Используйте F9 разумно и ограничивайте количество ячеек с формулой =МестноеВремя(...).

Лучше всего работать с формулой =МестноеВремя(...) в книге Excel, где нет макросов VBA, обрабатывающих ячейки циклами. Иначе, каждая итерация, изменяющая ячейку, будет сопровождаться пересчетом всех формул =МестноеВремя(...), и цикл может затянуться на неопределённое время.

Мы создали гибкое и бесплатное решение для получения точного времени в любых городах мира прямо в Excel. Код VBA использует открытый API, поэтому не требует регистрации, ключей или оплаты. Список городов легко дополнять — достаточно добавить новую строку с данными в таблицу Таблица1, и новый город автоматически появится в выпадающем списке.

Такой инструмент будет полезен для всех, кто часто работает с разными часовыми поясами.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.