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

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

googlespreadsheet_utc_timezone_convert_topimage

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時間遅れてる事を示します。

グリニッジ標準時です。GMT(Greenwich Mean Time)


そしてもう一つ世界標準時として定められているのが協定世界時(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-master.zipを展開すると「timezones.csv」が見つかるので、Googleスプレッドシートにインポートします。

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

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

「新しいシートを挿入する」を有効


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

新しいシート「timezones」が追加されUTC一覧が読み込まれました

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

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

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

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

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

=(VLOOKUP(B2,timezones!A$1:C$589, 2, FALSE)/86400)
ヒットした行の[offset]UTC時差を取得


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

タイムゾーン「Europe/Paris」のUTC時差「1:00」を表示


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

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

=(A2-"9:00"+C2)
日本日付時間から「Europe/Paris」の日付時間に変換


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

日付時間がタイムゾーン「Europe/Paris」の日付時間に変換されました
▲東京はフランス/パリより8時間進んでいます。

各地域の日付時刻に変更

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

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

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

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

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

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


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

ドロップダウンリストからタイムゾーンを選ぶだけで、日本日付時間から変換

まとめ

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

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

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





【UdemyのおすすめGoogleスプレッドシート学習コース】

ネットショッピングをする感覚で講座を購入し、収録された動画を視聴するスタイルなので自分のペースで学習できるUdemy

そのUdemyにはGoogleスプレッドシートが学べる講座もあります。 Googleスプレッドシートの基本から一歩踏み込んだところまでを学べる評価の高い講座です。スプレッドシート以外の表計算ソフトでも使えるテクニックが習得できるので、コスパも高く実務ですぐに使えます。

【売上分析で売上を最大化】ゼロから始めるGoogleスプレッドシートマスターコース|最先端技術で売上最大化&無駄の最小化

初心者でも安心!スプレッドシートをマスターし『売上の最大化』と『無駄の最小化』ができるようになるコースです。 スプレッドシートの最新技術をマスターし、Excelよりも効率的に最小の労力で最大の結果を出せる分析能力を身につけましょう!

【IT初心者でもわかる!Google スプレッドシート入門編】基本機能を活用したワンランク上の使い方、情報処理をマスター

Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。 四則演算や絶対参照、キーボードショートカットからはじまり、 関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験

【業務自動化をゼロからマスター】Google Apps Scriptの仕事効率化・自動化をプログラミングで実現

Google Apps Script(GAS)をマスターして毎日の業務(gmail、google スプレッドシート)を劇的に自動化させよう。



BTOパソコンの草分け的ブランドFRONTIER(フロンティア)

3DCG/動画編集/そしてゲームプレイに。
用途に合わせてCPUやGPUなどパーツを自由に選べて、コスパにもこだわる方にオススメ。

半期決算売り尽くしセール!開催中\RTX 30シリーズ搭載PCが13万円台~!/8/6(金)15時まで

特典付き&メモリ無償アップが大好評。

Core i5-10400F + RTX 3060 Ti搭載モデルが[15万円台]
Core i9-11900F + RTX 3080 Ti搭載ハイエンドモデルが[33万円台]

見逃し厳禁!SALE製品は台数に限りがあるので売り切れる前に是非

\MSIとFRONTIERがコラボした特別仕様のゲーミングPCが発売開始/
人気のMSI製マザーボード、 グラフィックスカード、水冷CPUクーラー、電源をMSI製ケースに搭載した 特別仕様のゲーミングPCが発売中です

>>【FRONTIER】


【デジハリONLINEの 基礎動画 + AdobeCC一年ライセンス のお得プラン】

デジタルハリウッドのオンラインCGスクール「デジハリONLINE」が提供する、60時間の基礎動画とAdobe creative cloudの一年版ライセンスがパッケージされた、コスパ最強の講座プラン

「Adobeマスター講座」

受講ノルマは一切無し、Adobe製品の利用を目的に契約してもOK!

>>デジハリONLINE講座申込

▶︎基礎動画教材付きAdobe Creative Cloudを年額39,980円(税込)のお得な価格で使用する方法


Twitter Facebook Pocket LINE はてブ

おすすめの関連記事
コチラの記事も読まれています
あわせてよく読まれている記事