На глаза попалась таблица, созданная подразделением Greenwich2000GreenwichMeanTime.com.

Функционал, думаю, объяснять подробно не нужно – мы имеем что-то вроде таблицы умножения: подписи сверху и снизу отражают часовые пояса, а основную область занимает время с учетом отклонения от нулевого меридиана.

Чего в таблице недостает – так это расчета времени во всех часовых поясах на основании текущего времени в часовом поясе пользователя.

Итак, задача:

  • Предоставить пользователю возможность выбора его часового пояса.
  • Обеспечить расчет времени во всех часовых поясах на основании текущего времени с учетом выбранного часового пояса пользователя.

Справочник часовых поясов

Чтобы выполнить первый пункт, нам необходим справочник основных городов мира с указанием их часовых поясов – наподобие того, что есть в Windows. Покопавшись в сети, можно найти справочник, разбитый по странам:

США и КАНАДА

  • США — Hawaii Time (GMT-10)
  • США — Alaska Time (GMT-9)
  • США — Pacific Time (GMT-8)
  • США — Mountain Time (Феникс) (GMT-7)
  • США — Mountain Time (GMT-7)
  • США — Central Time (GMT-6)
  • США — Eastern Time (GMT-5)
  • Канада — Pacific Time (GMT-8)
  • Канада — Mountain Time (GMT-7)
  • Канада — Central Time (Реджайна) (GMT-6)
  • Канада — Central Time (GMT-6)
  • Канада — Eastern Time (GMT-5)
  • Канада — Atlantic Time (GMT-4)
  • Канада — Newfoundland Time (GMT-3:30)

ЕВРОПА

  • Исландия (GMT+0)
  • Великобритания, Ирландия, Португалия (GMT+0)
  • Испания, Франция, Германия (GMT+1)
  • Греция, Финляндия, Турция (GMT+2)
  • Израиль (GMT+2)

АЗИЯ

  • Ближний Восток (GMT+3)
  • Ближний Восток (GMT+3:30)
  • Ближний Восток (GMT+4)
  • Ближний Восток (GMT+4:30)
  • Ближний Восток (GMT+5)
  • Азия (GMT+5:30)
  • Азия (GMT+6)
  • Азия (GMT+6:30)
  • Азия (GMT+7)
  • Азия (GMT+8)
  • Азия (GMT+9)

РОССИЯ

  • Калининград (GMT+2)
  • Москва (GMT+4)
  • Самара (GMT+4)
  • Екатеринбург (GMT+5)
  • Новосибирск (GMT+6)
  • Красноярск (GMT+7)
  • Иркутск (GMT+8)
  • Якутск (GMT+9)
  • Владивосток (GMT+10)
  • Магадан (GMT+11)
  • Петропавловск-Камчатский (GMT+12)

СНГ

  • Беларусь (GMT+2)
  • Молдова (GMT+2)
  • Украина (GMT+2)
  • Грузия (GMT+4)
  • Армения (GMT+4)
  • Азербайджан (GMT+4)
  • Туркменистан (GMT+5)
  • Узбекистан (GMT+5)
  • Таджикистан (GMT+5)
  • Казахстан (GMT+6)
  • Кыргызстан (GMT+6)

СТРАНЫ БАЛТИИ

  • Эстония (GMT+2)
  • Латвия (GMT+2)
  • Литва (GMT+2)

АВСТРАЛИЯ

  • Австралия — Западная Австралия (GMT+8)
  • Австралия — Южная Австралия (GMT+9:30)
  • Австралия — Северные Территории (GMT+9:30)
  • Австралия — Квинсленд (GMT+10)
  • Австралия — Тасмания (GMT+10)
  • Австралия — Виктория (GMT+10)
  • Австралия — Новый Южный Уэльс (GMT+10)
  • Новая Зеландия (GMT+12)

ДРУГИЕ СТРАНЫ

  • Мексика (GMT-6)
  • Центральная Америка (GMT-6)
  • Колумбия, Перу, Эквадор (GMT-5)
  • Венесуэла, Боливия (GMT-4)
  • Бразилия — Манаус (GMT-4)
  • Чили (GMT-4)
  • Парагвай (GMT-4)
  • Аргентина (GMT-3)
  • Уругвай (GMT-3)
  • Бразилия — Рио-Де-Жанейро (GMT-3)
  • Африка (GMT+0)
  • Африка (GMT+1)
  • Африка — Египет (GMT+2)
  • Африка — ЮАР (GMT+2)
  • Африка (GMT+3)

показать/скрыть

Разместим наш справочник на отдельном листе.

Из списка нам необходимо выделить текст, указанный в скобках – т.е. часовой пояс. Вручную делать это слишком утомительное занятие, поэтому прибегнем к формуле ПСТР, возвращающей заданное количество знаков из строки текста, начиная с указанной позиции.

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

=ПОИСК("(";A1), где A1 – ячейка с текстом

Если мы оставим формулу как есть, результирующий текст будет содержать скобку, поэтому прибавим к позиции единицу:

=ПОИСК("(";A1)+1

Длину результирующего текста можно узнать, вычтя из позиции закрывающей скобки позицию открывающей скобки:

=ПОИСК(")";A1)-(ПОИСК("(";A1)+1)

Итак, у нас есть все необходимые параметры для получения отклонения по времени:

=ПСТР(A1;ПОИСК("(";A1)+1;ПОИСК(")";A1)-(ПОИСК("(";A1)+1))

Из результирующей строки необходимо выделить знак отклонения и время отклонения.

Знак выделим следующим образом:

=ПСТР(B1;4;1), где B1 – строка, полученная в предыдущей формуле

Время (в данном случае мы можем не искать точное количество символов, а просто указать заведомо большее значение):

=ПСТР(B1;5;10)

Далее выделим из строки со временем часы и минуты в отдельные столбцы.

В итоге у нас должна получится такая таблица:

Справочник часовых поясов

Именованные диапазоны

Для удобства использования в дальнейшем присвоим диапазонам справочника имена.

Это можно сделать следующим образом:

  • Выбрать интересующий диапазон
  • В области имени ячейки изменить стандартное имя (например, A1) на произвольное и подтвердить изменения клавишей Enter.

Присвоение имени

Изменить именованные диапазоны можно в меню Вставка – Имя – Присвоить…

Присвоим имена диапазонам:

  • A2:A94 – диапазон с перечнем стран (group)
  • B2:B94 – отклонение, в часах (hours)
  • C2:C94 – отклонение в минутах (minutes)

Выбор часового пояса

Модифицируем внешний вид основной таблицы и добавим новый функционал.

Выделим на основном листе ячейку для выбора пользователем его часового пояса: выделим ее визуально, добавив границы, и в меню Данные - Проверка… (Проверка данных - в Excel 2007+) добавим условие с типом Список и укажем источник – наименование диапазона с перечнем стран (group).

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

Для удобства дальнейших расчетов выведем на основной лист отклонение. Оно должно быть связано с выбором пользователя. Здесь к нам придут на помощь следующие формулы:

  • ПОИСКПОЗ - поиска относительной позиции заданной строки в массиве,
  • ИНДЕКС – поиск строки в массиве по заданной позиции.

Выведем отклонение в часах:

=ИНДЕКС(hours;ПОИСКПОЗ(A1;group;0)), где A1 – ячейка, в которой пользователь осуществляет выбор часового пояса

И в минутах:

=ИНДЕКС(minutes;ПОИСКПОЗ(A1;group;0))

Отклонение от текущего времени

Сформируем таблицу часовых поясов, куда подставим текущее время на компьютере пользователя с учетом выбранного часового пояса. Первую строку заполним аббревиатурой GMT, вторую строку значениями часовых поясов от –12 до +12, в третьей строке будет рассчитываться текущее время во всех часовых поясах.

За базис возьмем время нулевого меридиана, которое посчитаем следующим образом:

=ТДАТА()+ЕСЛИ(G1>0;-ВРЕМЯ(ABS(G1);I1;0);ВРЕМЯ(ABS(G1);I1;0)), где G1 – отклонение в часах, I1 – отклонение в минутах

Разберем формулу подробнее:

  • ТДАТА() – текущие дата и время
  • ЕСЛИ(G1>0;(1);(2)) – если отклонение от нулевого меридиана больше нуля выполняем условие (1), если меньше нуля – выполняем условие (2)
  • (1) -ВРЕМЯ(ABS(G1);I1;0) – вычитаем из текущего времени отклонение, выбранное в справочнике
  • (2) ВРЕМЯ(ABS(G1);I1;0) – прибавляем к текущему времени отклонение, выбранное в справочнике

Присвоим ячейке имя time – его мы будем использовать в дальнейших расчетах.

В остальные ячейки введем формулу:

=time+ЕСЛИ(M2>0;ВРЕМЯ(ABS(M2);0;0);-ВРЕМЯ(ABS(M2);0;0)), где M2 – ячейка со значением часового пояса данного столбца (-12…+12)

  • time – имя ячейки текущих даты/времени с корректировкой на выбранный часовой пояс
  • ЕСЛИ(M2>0;(1);(2)) – если часовой пояс данного столбца больше нуля – выполняется условие (1), в противном случае выполняется условие (2)
  • (1) ВРЕМЯ(ABS(M2);0;0) – прибавляем к времени нулевого меридиана отклонение часового пояса данного столбца
  • (2) -ВРЕМЯ(ABS(M2);0;0) – вычитаем из времени нулевого меридиана отклонение часового пояса данного столбца

Результат вы можете скачать в формате Excel 2003, Excel 2007+

Время по Гринвичу

Добавить комментарий


Защитный код
Обновить

Вверх