オフィスソフト

「Excel」でシリアル値から日付と時刻を抽出--TRUNC()関数とセルの書式設定の使用方法

「Excel」では日付のデータが「44292」のような形で表示されることがある。このようなシリアル値から日付と時刻を抽出して、日付と分かる形式で表示する方法を紹介する。

 「Excel」シートでは、日付や時刻の処理がよく必要になる。運が良ければ、自分のシートの使い方に対応した構造になっていることもあるが、シートを引き継ぐ場合もあるし、新しい要求に対応しなければならないこともある。簡潔に言うと、日付のシリアル値に日付と時刻の両方が含まれている場合と、一方しか含まれていない場合がある。最終的には、日付や時刻をその両方が含まれるシリアル値から抽出する方法を知ることが必要になるかもしれない。本記事では、日付と時刻の両方に対応できる簡単なTRUNC()関数を紹介する。

 筆者は「Windows 10」の64ビット版システムで「Microsoft 365」を使用しているが、古いバージョンを使っても構わない。作業用のデータは自分で用意してもいいし、デモ用の.xlsxファイルや.xlsファイルをダウンロードしてもいい。本記事で紹介する数式は、ブラウザー版でもサポートされている。

Excelでの日付演算について

 Excelを初めて使う人でなければ、Excelがシリアル値を使用して日付と時刻を格納する仕組みについて多少は知っているはずだ。このセクションで簡単におさらいしてみよう。

 日付は通常、たとえば「2021年4月6日」や「2021/4/6」のように、予想どおりの形で表示される。人間が理解できる日付の形式は他にもあり、特定の日付と時刻の形式を使用して、さまざまな方法で日付や時刻を表示できる。だが、Excelはこのような形で日付を保存しているわけではない。ユーザーが目にする形式の文字列としてではなく、シリアル値として格納している。1900年1月1日を日付のシリアル値の1として、1日進むごとに1を追加していく。計算してみると、2021年4月6日の日付のシリアル値は44292であることが分かるだろう。

 これを自分で確かめるために、「2021年4月6日」という日付を入力してみよう。Excelには図Aのように表示される。これが英語のデフォルトの日付形式だ。表示が多少違っていても心配しなくていい。別のデフォルトで作業していることもあるからだ(誰かが変更した場合)。右側のセルは、同じ方法で入力した同じ日付だが、形式が異なる。ここでは、格納されたシリアル値が分かるように、表示形式を「標準」に変更した。日付の文字列を入力すると、形式が日付として設定され、「d-mmm-yy」という日付形式が適用される。

図A

これらの2つのエントリーは、Excelには同じものとして格納される。
これらの2つのエントリーは、Excelには同じものとして格納される。

 日付は整数で、時間は小数の値だ。1日は24時間に等しい。12時間は半日、すなわち0.50だ。詳細な分析については、表Aを参照してほしい。次のセクションでは、完全なシリアル値、つまり日付と時間の両方を含む日付を扱う。

表A

形式 h:mm:ss

時間のシリアル値

相当する時間

23:59:59

0.999988426

23時間59分59秒

1:00:00

0.041666667

1時間

23:00:00

0.958333333

23時間

0:01:00

0.000694444

1分

0:59:00

0.040972222

59分

0:00:01

1.15741E-05

1秒

0:00:59

0.00068287

59秒

Excelで日付を抽出する方法

 皆さんがよく扱うのは、小数の値、すなわち時刻の値を持たない日付の値だろう。2つが結合されて、44292.0412のような1つの値になっている場合は、処理が難しくなるかもしれない。たとえば、44292.0412、44292.0413、44292.000695は、日付の部分は同じだが、同じ値ではない。図Bには、結合されたシリアル値が3つある。ここでも、日付をリテラル日付文字列として最初の列に入力した。シリアル値を確認できるように、C列に「標準」表示形式を適用している。

図B

日付を抽出する。
日付を抽出する。

「オフィスソフト」で読まれている記事

TechRepublic Japanで人気の記事

編集部オススメ

トレンドまるわかり![PR]

財務・経理
人事・労務
マーケ・営業
購買・調達
生産・製造
データ分析
コミュニケーション
通信・通話
文書・コンテンツ
PC・モバイル
PC
スマートフォン
タブレット
ウェアラブル
オフィスソフト
OCR
RPA
PCソフト
周辺機器
新興技術
ITインフラ
クラウドサービス
OS・ミドルウェア
開発
データベース
運用
セキュリティ

ホワイトペーパーランキング

  1. 最先端のデータサイエンティストでいるための5つのヒント—AIによる高度化でデータの達人であり続ける
  2. 経理部門 554人に聞いた「新しい経理部門の働き方」 その実現に向けた具体的な行動指針を解説
  3. パンデミックに乗じたサイバー攻撃に屈しない 最新の脅威分析レポートに見る攻撃パターンと対応策
  4. DX時代にIBM i は継続利用できるのか? モダナイゼーション実施で考えておくべき5つの視点
  5. サイバー攻撃でPCに何が起きている? サイバーディフェンス研究所の名和氏が語るフォレンジックのいま

Follow TechRepublic Japan

このサイトでは、利用状況の把握や広告配信などのために、Cookieなどを使用してアクセスデータを取得・利用しています。 これ以降ページを遷移した場合、Cookieなどの設定や使用に同意したことになります。
Cookieなどの設定や使用の詳細、オプトアウトについては詳細をご覧ください。
[ 閉じる ]