Excelでは日付と時間(時刻)をシリアル値と呼ばれる特殊な数値で管理しており、あらかじめ用意された書式や関数を使用することによって様々な形式で表示したり計算をすることができます。
シリアル値とは
シリアル値とは1900年1月1日0:00を1として経過日数や時間を管理する特殊な数値です。
1900年1月2日0:00のシリアル値は2、1月3日0:00は3となります。
時間(時刻)について
経過日数1日がシリアル値の1となるため、1:00(1時間)は1/24(0.041667)、23:00(23時間)は23/24(0.958333)、24時間で1となります。
(例)1900年1月2日 1:00のシリアル値は2.041667
表示形式について
セルに「年/月/日」「時:分:秒」と入力すると日付・時刻データとして認識され、自動でシリアル値に変換されます。ただし、セルの書式設定が文字列になっている場合は変換されません。
(例)入力したデータが「1900/1/2 1:00」の場合
シリアル値に変換されているため書式設定によって以下のとおり表示を変更できます。
yyyy/m/d h:mm → 1900/1/2 1:00(入力したとおりに日付と時刻を表示)
yyyy/mm/dd → 1900/01/02
m/d”(“aaa”)” → 1/2(月)
yyyy”年”m”月”d”日(“aaaa”)” → 1900年1月2日(月曜日)
yyyymmdd → 19000102
h:mm:ss → 1:00:00
※和暦の表示も可能ですが、ここでは割愛します
時間の計算をする際の注意点
時は24進数、分と秒は60進数となるため合計時間が24時間や60分を超えると自動的に繰り上げされ、端数のみが表示されるという現象が起こります。
(例)時間の合計が30時間となった場合に「6」と表示されるなど
書式を「d”日と”h”時間”」としておけば「1日と6時間」のように正確に表示はできますが、あまり実用的ではありません。そのため、以下のように設定する必要があります。
[h] → 30と表示される
(例)合計時間が100分になる場合
[m] → 100と表示される
日付に関する便利な関数まとめ
日付データ(シリアル値)から年、月、日を取り出す
YEAR(シリアル値) → 「年」を取得
MONTH(シリアル値) → 「月」を取得
DAY(シリアル値) → 「日」を取得
WEEKDAY(シリアル値) → 日曜日「1」～土曜日「7」までの数値を返す
文字列データを日付データ(シリアル値)に変換する
DATE関数
DATE(年,月,日)
(例)DATE(1900,1,1) → 日付データ「1900/1/1」(シリアル値:1)に変換される
DATEVALUE関数
DATEVALUE(日付文字列)
(例)DATEVALUE(“1900/1/1”) → 1(シリアル値)に変換される
年月日を個別に指定してシリアル値に変換する場合はDATE関数、「●●●●/●●/●●」のように日付を表す文字列を作成してから変換する場合はDATEVALUE関数を使います。
経過年月日を取得する
単純な日数計算であれば日付データ(シリアル値)どうしを引き算すれば正確な経過日数を算出できますが、年齢など経過年数や月数を算出したい場合はそのまま引き算しても正確な値とはなりません。
そのため、経過年月日を取得する場合は基本的にDATEDIF関数を使用します。
DATEDIF関数
DATEDIF(開始日,終了日,表示形式)
(例1)年齢を取得する
開始日：2002/4/1
終了日：2024/5/19
DATEDIF(開始日,終了日,”y”) → 22
(例2)経過年月日を取得する
開始日：2002/6/20
終了日：2024/5/19
経過年数を取得する
DATEDIF(開始日,終了日,”y”) → 21
経過月数を取得する
DATEDIF(開始日,終了日,”m”) → 262 トータル月数を取得
DATEDIF(開始日,終了日,”ym”) → 10 12ヶ月で年に繰り上げ、端数(月数)を取得
経過日数を取得する
DATEDIF(開始日,終了日,”d”) → 8004 トータル日数を取得
(シリアル値は1日=1となるため、終了日－開始日としても同じ結果になります)
DATEDIF(開始日,終了日,”md”) → 29 28～31日で月に繰り上げ、端数(日数)を取得
経過年月日を正確に取得したい場合は以下のように数式を入力します。
DATEDIF(開始日,終了日,”y”)&”年”&DATEDIF(開始日,終了日,”ym”)&”ヶ月”&DATEDIF(開始日,終了日,”md”)&”日”
上記(例2)の場合は「21年10ヶ月29日」と表示されます。
指定した期間内の稼働日数(営業日)を取得する
開始日から終了日までの土日や祝祭日を除いた稼働日数(営業日)を取得します。
NETWORKDAYS関数
NETWORKDAYS(開始日,終了日,祝祭日)
※祝祭日は日付データを入力したセル範囲を指定することで稼働日数から除外できます。
(例)
開始日：2024/5/1
終了日：2024/5/10
土日だけを除外する場合
NETWORKDAYS(開始日,終了日) → 8
土日、祝日、祭日を除外する場合
NETWORKDAYS(開始日,終了日,祝祭日のセル範囲) → 6
指定日数後の稼働日(営業日)のシリアル値を取得する
開始日と日数を指定することで、指定日数後の稼働日(営業日)をシリアル値で取得できます。
WORKDAY関数
WORKDAY(開始日,日数,祝祭日)
(例)
開始日：2024/5/1
日数：2日後
WORKDAY(開始日,2,祝祭日のセル範囲) → 2024/5/7
時間(時刻)に関する便利な関数まとめ
時刻データ(シリアル値)から時、分、秒を取り出す
HOUR(シリアル値) → 「時」を取得
MINUTE(シリアル値) → 「分」を取得
SECOND(シリアル値) → 「秒」を取得
文字列データを時刻データ(シリアル値)に変換する
TIME関数
TIME(時,分,秒)
(例)TIME(12,25,30) → 時刻データ「12:25:30」(シリアル値：0.517708)に変換される
TIMEVALUE関数
TIMEVALUE(時刻文字列)
(例)TIMEVALUE(“12:25:30”) → 0.517708(シリアル値)に変換される
最後に
日付と時刻(時間)に関してはシリアル値を理解することがとても重要となるため、しっかりと理解しておきましょう。
コメント