Excel日付時刻関数

はじめに

事務処理業務にて、日付や時刻データを扱うことが多いのですが、その時に活用するのが日付・時刻関数です。Excelでは、セルに「8/22」や「16:30」と入力すると、それぞれ日付と時刻のデータと認識されます。実際にはシリアル値(整数部分は日付、実数部分は時刻)がデータとして入力され、それを「日付」または「時刻」の表示形式にて表示されています。

次図の例のように、任意のセルに今日の日付、例えば「8/22」と入力すると、セルには「8月22日」と「何月何日」の形式で表示されます。これは、Excelが「8/22」は日付と判断しているからです。「8/34」とか「13/4」などは日付としてあり得ないので、日付データとしては認識されません。

この日付データが入っているセルに数値「100」を入力したら、「4月9日」と表示されます。これは何故でしょうか。

ex-date01

[本テキスト用データダウンロード]
ex_datetime1.xlsx

この「4月9日」と表示されたセルを選択し、数式バーを確認すると、そこには「1900/4/9」と表示されています。つまりこの「4月9日」は1900年の4月9日を示しています。また、このセルの表示形式を「標準」に戻すと、「100」と表示されます。

実は、Excelの場合、「1900年1月1日」を「1」として、1日経過するごとに「1」ずつ増える連番(シリアル値)で日付データとして持っています。なので、シリアル値「100」は、「1900年1月1日」を含めて100日目の「1900年4月9日」ということになるのです。例えば2008年1月1日は、1900年1月1日から39,448 日後に当たるので、シリアル値は「39448」になります。
(ただし、「1900年2月29日」という存在しない日もカウントされていることに注意。1900年はうるう年ではありません。)

今度は、任意のセルに「18:00」と入れてみます。この場合、Excelはこれを時刻データと認識し、表示形式が「時刻(実際は、「ユーザー定義」)」になります。これを「標準」の表示形式に戻すと「0.75」と表示されます。

時刻の場合、Excelは一日(24時間)を「1」とした実数で表します。「18:00」は、一日を3/4経過しているので「0.75」というわけです。「0.5」は1日のちょうど半分だから「正午」ということになります。

まとめると、日付データは整数のシリアル値、時刻データは実数のシリアル値で扱います。つまり「100.75」は、「1900年4月9日の18:00」を意味することになります。


日付関数の例1(日付及び時刻のシリアル値を返す)

日付・時刻からシリアル値を返す関数に、TODAY関数とNOW関数があります。いずれのシステムの日時データから取得します。

構文

TODAY() ... 常に今日のシリアル値を返す
NOW() ... 現在の日時のシリアル値を返す

ex-date02

任意の日付・時刻からシリアル値を返す関数に、DATE関数とTIME関数があります。

構文

DATE(年, 月, 日) ... 任意の日付(年月日)のシリアル値を返す
TIME(時, 分, 秒) ... 任意の時刻(時分秒)のシリアル値を返す

ex-date03

日付時刻関数の例2(シリアル値から日付または時刻を取得する)

シリアル値から年月日あるいは時分秒を取得する関数があります。

構文

YEAR(シリアル値) ... シリアル値から「年」を取得する
MONTH(シリアル値) ... シリアル値から「月」を取得する
DAY(シリアル値) ... シリアル値から「日」を取得する
HOUR(シリアル値) ... シリアル値から「時」を取得する
MINUTE(シリアル値) ... シリアル値から「分」を取得する
SECOND(シリアル値) ... シリアル値から「秒」を取得する

ex-date04

日付時刻関数の例3(曜日情報を取得する)

シリアル値から曜日情報を取得する関数としてWEEKDAY関数があります。WEEKDAY関数は曜日を表す数値(例: 1…日曜、2…月曜、… 7…土曜)を返します。

構文

WEEKDAY(シリアル値, [週の基準]) ... シリアル値から曜日を表す数値を返す

[週の基準]

1(省略)... 1(日曜)~7(土曜)
2 ... 1(月曜)~7(日曜)
3 ... 0(月曜)~6(日曜)
他

シリアル値から曜日を取得する方法として、このWEEKDAY関数とCHOOSE関数を組み合わせて利用する方法と、TEXT関数にてシリアル値を曜日の表示形式で文字列化する方法の2種類があります。

  • =CHOOSE(WEEKDAY(シリアル値),”日”,”月”,”火”,”水”,”木”,”金”,”土”)
  • =TEXT(シリアル値,”aaa”)

ex-date05


練習課題1

  • Q1. 任意の起算日から100日目の日を算出する
    • 起算日に100を加算する
  • Q2. 東京オリンピック開幕(2020年7月24日開幕式)まで後何日か算出する
    • 開幕の日から今日の日付を引く
  • Q3. 生年月日からその人の年齢と生まれた日の曜日を算出する
    • 年齢算出にはDATEDIF関数を使用する

ex-date-sub01

  • Q4. 「5時間55分24秒+6時間39分32秒」の計算結果を表示しなさい。
  • Q5. 「15時間55分+10時間39分+11時間0分」は何時間何分か表示しなさい。
    • とりあえず作業領域のセルに、3つの時間の合計を計算し、そのシリアル値から解決方法を導くこと

ex-date-sub02


練習課題2

次図のような、スケジュール管理(工程管理)を行うワークシートを作成してください。ただし、次のような条件(仕様)を満たすものとします。

  1. 後で他の作業内容を追記できるよう、設定した計算式はすべての行に入力する。
  2. 先頭の日付(G3、G4)を変更したら、それ以降の日付も更新される。
  3. 「曜(曜日)」は自動的に表示される。(土日についてはセルの色を変える。)
  4. 「開始日」と「終了日」から作業「日数」を計算する。
  5. 「開始日」と「終了日」を入力したら、対応する日付のセルに色を付ける。

ex-date-sub03

[ヒント]

  • 日付の上の行のセル(G2~U2)にシリアル値を入れておき、非表示にしておく。(非表示の表示形式は「;;」)
  • シリアル値から「1日」の場合だけ月が表示されるよう、IF関数やMONTH関数などを使用する。
  • 条件付き書式設定機能の数式によってセルの塗り潰しの色を変える。

以下は、なるべく見ないで練習課題に挑戦してください。

練習課題1の解答例

各問いの数式例は以下の通り。

  • Q1
    • C6の式: =B6+100
  • Q2
    • C10の式: =TEXT(B10-TODAY(),"あと#日")
  • Q3
    • C14の式: =DATEDIF(B14,TODAY(),"Y")
    • D14の式: =TEXT(B14,"aaaa")
  • Q4
    • B18の式: ="5:55:24"+"6:39:32"
  • Q5
    • B25の式: ="15:55"+"10:39"+"11:00"
    • B22の式: =INT(B25)*24+HOUR(B25)
    • D22の式: =MINUTE(B25)

練習課題2の解答例

各数式例は以下の通り。

  • 「日数」
    • F6の式: =IF(OR(D6="",E6=""),"",E6-D6+1)
  • 「日付(シリアル値)」
    • G2の式: =DATE(2017,G3,G4)
    • H2の式: =G2+1(以降、右の列にコピー)
  • 「曜日」
    • G5の式: =TEXT(G2,"aaa")
  • 「月」
    • H3の式: =IF(DAY(H2)=1,MONTH(H2),"")
  • 「日」
    • H4の式: =DAY(H2)

条件付き書式設定例は以下の通り。

  • 土日の日付の塗りつぶし
    • 適用先: =$G$3:$U$5
    • 書式ルール(数式): =OR(G$5="土",G$5="日")
  • 工程期間の塗りつぶし
    • 適用先: =$G$6:$U$10
    • 書式ルール(数式): =AND(G$2>=$D6,G$2<=$E6)

Excel日付時刻関数」への2件のフィードバック

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中