[Googleスプレッドシート]世界標準時を使って地域毎の日付時刻に時差変換する方法

Googleスプレッドシートでセルに入力した日付時刻を、時差がある地域の日付時刻に変換する方法を紹介します。

海外出張・旅行の予定表、他国の現地スタッフとのミーティング日時の設定など、国をまたいだ移動や予定の場合、時差を気にする必要があります。Googleスプレッドシートで予定表を作成する場合に簡単に時差変換の処理ができたら良いのですが、残念ながらセルに入力した日付時刻を他国の時刻に変換する機能は、Googleスプレッドシートにはありません。

Googleスプレッドシートの変更履歴や時間関係全般を管理するタイムゾーンを変更する事は出来ますが、セルに入力した日付時刻を指定した地域に合わせて変換するには、少し工夫が必要になります。

日付時刻を入力するだけで時刻変換してくれるウェブサービスを使えば、簡単に変換した結果を得る事はできますが、回数が多い場合は一つ一つ入力する必要があるので面倒。予定表を作成するGoogleスプレッドシートで直接変換できたら便利なはず。

そこで今記事では、Googleスプレッドシートで世界標準時のデータを利用し、入力した日付時刻を他のタイムゾーンの日付時刻へ変換する方法を紹介します。

協定世界時(UTC : Coordinated Universal Time)

世界標準時としてよく知られるのが グリニッジ標準時です。GMT(Greenwich Mean Time)と表記され、目にする機会も多いのではないでしょうか。例えばGoogle検索で”アメリカ合衆国の時刻”で検索すると各都市名の横に(GMT-6)や(GMT-10)と表示されます。これはGMT(Greenwich Mean Time)との時差を示していて、(GMT-6)ならグリニッジ標準時に対して6時間遅れてる事を示します。

そしてもう一つ世界標準時として定められているのが協定世界時(UTC : Coordinated Universal Time)です。
世界的に統一されたより精細な時刻利用が望まれ、1973年にすべての国の標準時の基礎として、UTC を採用することが勧告されました。

UTCタイムゾーンデータ一覧をGoogleスプレッドシートに読み込む

時差変換を行う為にはタイムゾーン毎の時差の情報が必要なので、タイムゾーンの情報が書かれたデータを用意します。

タイムゾーンの一覧を公開してるサイトは多数ありますが、今記事ではGitHubでダウンロード出来る、UTCからの秒単位のオフセットと、UTCからのサマータイムの間のオフセットが記されたタイムゾーンデータ一覧のCSVファイルを使います。

GitHubのタイムゾーン一覧CSVファイルが公開されているページはコチラ
⇒https://github.com/bproctor/timezones

右上にある[Code]からDownload ZIPをクリック、フォルダを指定してtimezones-master.zipをダウンロードします。

timezones-master.zipを展開すると「timezones.csv」が見つかるので、Googleスプレッドシートにインポートします。

Googleスプレッドシートのメニューから　ファイル > インポート　を選択。
「ファイルをインポート」のアップロードでtimezones.csvをインポートします。

場所のインポート項目で「新しいシートを挿入する」を有効に。「データをインポート」をクリック。

新しいシート「timezones」が追加されUTC一覧が読み込まれました。
[タイムゾーン]と[UTC時差][夏時間のUTC時差]が秒単位で表記されています。

UTCタイムゾーンデータ一覧で日付時刻を変更

インポートしたUTC一覧を使って日付時刻を変更したいと思います。

最初に数式の関数 VLOOKUP()を使ってUTC一覧から指定したタイムゾーン名を検索、ヒットした行の[offset]UTC時差を取得します。

VLOOKUP(検索キー, 範囲, 値を返す列の番号, [近似照合の有無])

第1引数にタイムゾーン名、第2引数にtimezonesシートのUTC一覧、第3引数に左から2列目[offset]列、を指定しました。
(夏時間を取得したい場合は第3引数に左から3列目[offset_dst]列を指定します。)
次に取得する[offset]の値は秒数なので、取得した値を１日の秒数86400で割り、シリアル値を算出します。

=(VLOOKUP(B2,timezones!A$1:C$589, 2, FALSE)/86400)

シリアル値は一秒0.000011574で換算されますが、算出された表記のままでは使用できないので、時間表記に変更します。シリアル値が表示されたセルを選択し、メニューから 表示形式 > 数字 >  時間または秒を省いた15:59を選択。タイムゾーン「Europe/Paris」のUTC時差「1:00」が表示されました。

次はいよいよ、日本日付時間から「Europe/Paris」の日付時間に変換します。

日本日付時間から日本のUTC時差”9:00″を引き同日付時間の協定世界時(UTC)を取得、そして変換するタイムゾーン「Europe/Paris」のUTC時差「1:00」を加えます。

=(A2-"9:00"+C2)

日本の日付時間がタイムゾーン「Europe/Paris」の日付時間に変換されました。

各地域の日付時刻に変更

入力した日付時刻を他地域のタイムゾーンへ変更する事ができました。しかしこのままでは、タイムゾーン名を都度timezonesシートのUTC一覧から見つける必要があり使い勝手が良くありません。
タイムゾーン名を指定しやすくする為に、ドロップダウンリストを作成、全タイムゾーンを選択候補に登録します。

タイムゾーンを選択するセル範囲を選択しメニューから　データ > データの入力規則　を選択。

ドロップダウンリストの選択候補にtimezonesシートのUTC一覧を指定します。

=timezones!$A$2:$A$589

セル範囲、ドロップダウンリストの選択候補「timezones!$A$2:$A$589」を設定したら、「決定」をクリック

「UTC」列と「現地日付時間」列に変換の数式が入力されていれば、ドロップダウンリストからタイムゾーンを選ぶだけで、日本日付時間から変換された「UTC時差」と「現地日付時間」がセルに入力されます。

まとめ

Googleスプレッドシートに入力した日付時刻を、別のタイムゾーンの日付時刻に変換する方法を紹介しました。
スプレッドシートの機能や数式の関数にはタイムゾーン変換の機能が無いので、時差変換を諦めていた人も多かったかも知れません。しかし記事内で紹介した方法であれば簡単に時差変換が可能になります。

今記事で使用したUTC一覧に限らず、タイムゾーン一覧のデータさえ用意できれば、時差に関わる問題は殆ど解決出来るのではないでしょうか。タイムゾーン一覧の時差表記も、データによっては秒数ではなく”9:00″のような時間表記の場合もあり、その場合はシリアル値の算出を等をする必要が無いので、より簡単・シンプルに扱うことが出来るので、扱いやすいタイムゾーン一覧データを探してみるのも良いと思います。

GASでスクリプトを作成する方法もあるとは思いますが、今記事で紹介したようにタイムゾーン一覧データをインポートさえすれば、簡単な数式だけで時差変換が出来てしまうので是非試してみてください。

𝕏 ( 旧Twitter ) Facebook Pocket LINE はてブ