【Excel LAMBDA】日付・時刻操作編（その１）【ユーザー定義関数集】

日付・時刻操作関数集その１

LAMBDA 関数を使った数式によるユーザー定義関数（カスタム関数）として、実用的でコピペで使えるサンプルコードをまとめていきたいと思います。

ユーザー定義関数の登録方法は以下の記事を参照してください。

www.shegolab.jp

本記事では日付・時刻の操作関連のツールとしてよく使いそうな機能を主に集めました。

とはいえ、Excel での実業務には疎いので本当に必要とされているものが足りないかもしれません。もし、こんな関数が欲しいという要望があったらお知らせください。

日付・時刻操作関数集その１
AFE ソース
関連記事
変更履歴

【使用上の注意】

本記事に掲載している数式は LAMBDA 関数をサポートしてる Excel（現状、 Microsoft 365 版（サブスク版）Excel のみ）で使用可能です。

本ブログに記載のLAMBDA 数式は全て単独で利用できます。必要な関数のみを「名前の定義」で「参照範囲」にコピー＆ペーストしてください。

関数名となる「名前」は一例を示していますが、任意の名前をつけて問題ありません。

不具合、使い勝手の改善、欲しい機能等ご意見ご希望がありましたら、本記事のコメントか Twitter にてお知らせください。

日付のみ

【名前の定義】

名前
TODATE
コメント
日付のみをあらわすシリアル値に変換します
参照範囲

=LAMBDA(シリアル値, INT(シリアル値))

【構文】

TODATE(シリアル値)

【引数】

引数	内容
シリアル値	時刻を含むシリアル値、あるいは日時を書式化した文字列

【戻り値】

日付のみのシリアル値

【説明】

指定のシリアル値から時刻を切り捨てることで、日付のみを表すシリアル値を返します。例えば「2022/5/1 12:34:59 」を表すシリアル値を TODATE 関数に与えると、「2022/5/1 00:00:00 」のシリアル値を返します。

引数にはシリアル値だけでなく、日時を表す文字列を渡すこともできますが、日時の書式になっていない場合は #VALUE! エラーとなります。

【使用例】

使用例	結果	コメント
=TODATE(NOW())	2022/5/1	現在日時のシリアル値から日付のみを取り出す。TODAY()と同じ結果
=TODATE("2022/5/1 23:59:59")	2022/5/1	日時書式の文字列

【解説】シリアル値とは

Excel で日付や時刻のデータを扱っていると、たまにナゾの数値に化けてしまって困ることがあります。
　

　
これは「シリアル値」という、Excel で日付や時刻を表す生データです。
　
Excel からすればシリアル値は単なる数値です。それが日付や時刻に見えるのは、セルの書式（表示形式）のおかげです。
　

　
日付や時刻の表示形式は、単なる数値をシリアル値として解釈し、日時書式で表示しているのです。
　
ではこのシリアル値というのが具体的に何から来た値のかというと、「1900年1月1日」から数えた経過日数として定義されます。
　
つまり「1」を日付で表示すると「1900/1/1」になり、「2」は「1900/1/2」になります。「365」ではどうでしょう。「1900/12/30」になりますね。1900 年は閏年なので「366」で「1900/12/31」になります。
　
では現在の日付のシリアル値はどれくらいになるかというと、「2022/5/1」なら「44682」です。そう、これがナゾの数値の正体です。
　
シリアル値は日付だけでなく時刻のデータも表します。時刻は１日に満たない中途半端な値、すなわち小数として表されます。
　
つまり小数点以下のシリアル値は１日24時間を占める割合に対応し、例えば「0.5」なら24時間の半分の正午12時（12:00:00）、「0.25」なら午前６時（6:00:00）となります。
　
日時は、日付の整数と時刻の小数を合わせることで表現されます。例えば「2022/5/1 6:00:00」なら「44682.25」となります。　

　
普段、日付や時刻を扱っている分にはシリアル値を意識するはありませんが、日付と時刻の足し算や引き算といった計算はシリアル値のおかげでシンプルに扱うことができるのです。

時刻のみ

【名前の定義】

名前
TOTIME
コメント
時刻のみをあらわすシリアル値に変換します
参照範囲

=LAMBDA(シリアル値, MOD(シリアル値, 1))

【構文】

TOTIME(シリアル値)

【引数】

引数	内容
シリアル値	日付を含むシリアル値、あるいは日時を書式化した文字列

【戻り値】

時刻のみのシリアル値

【説明】

指定のシリアル値から、日付の情報を持たない、時刻のみを表すシリアル値を切り出します。例えば「2022/5/1 12:34:59 」を表すシリアル値を TOTIME 関数に与えると、「12:34:56」のシリアル値を返します。（ちなみに日付としては「1900/1/0」と表示されます）

引数にはシリアル値だけでなく、日時を表す文字列を渡すこともできますが、日時の書式になっていない場合は #VALUE! エラーとなります。

【使用例】

使用例	結果	コメント
=TOTIME(NOW())	12:34:56	現在日時のシリアル値から時刻のみを取り出す
=TOTIME("2022/5/1 23:59:59")	12:34:56	日時書式の文字列

時間換算

【名前の定義】

名前
TOHOURS
コメント
シリアル値を時間単位に換算します
参照範囲

=LAMBDA(シリアル値, シリアル値 * 24)

【構文】

TOHOURS(シリアル値)

【引数】

引数	内容
シリアル値	日時のシリアル値、あるいは日時を書式化した文字列

【戻り値】

シリアル値を時間に換算した数値

【説明】

TOHOURS 関数はシリアル値を期間とみなし、時間（Hours）に換算した数値を返します。例えば「1:30」を表すシリアル値を TOHOURS 関数に与えると、「1.5」を返します。

【使用例】

使用例	結果	コメント
=TOHOURS("1:30")	1.5	1.5時間
=TOHOURS(終業時刻 - 始業時刻 - 休憩時間)	8.0	勤務時間の計算

漢数字の和暦に書式化

【名前の定義】

名前
KANJIWAREKI
コメント
日付のシリアル値を漢数字による和暦に書式化します
参照範囲

=LAMBDA(日付, LET(
    wareki, TEXT(INT(日付), "[DBNum1]ggge年m月d日"),
    isGannen, IFERROR(SEARCH("??一年", wareki)=1, FALSE),
    IF(isGannen,
        REPLACE(wareki, 3, 1, "元"),
        wareki
    )
))

【構文】

KANJIWAREKI(日時)

【引数】

引数	内容
日付	日付のシリアル値、あるいは日付書式の文字列

【戻り値】

指定の日付を漢数字による和暦で書式化された日付文字列。

【説明】

KANJIWAREKI 関数は日付の数字を漢数字で書式化します。

「令和元年」など、元号の1年目は「元年」となります。

日付が日付書式としてみなせない場合は #VALUE! エラーとなります。

【使用例】

使用例	結果	コメント
=KANJIWAREKI(TODAY())	令和四年五月五日
=KANJIWAREKI("2019/12/31")	令和元年十二月三十一日

日時文字列 ⇒ シリアル値

【名前の定義】

名前
DATETIMEVALUE
コメント
日時文字列をシリアル値に変換します。
参照範囲

=LAMBDA(日時文字列,
    LET(
        _isDigits, LAMBDA(s, NOT(OR(ISERROR(MID(s, SEQUENCE(LEN(s)), 1)+0)))),
        _leftBefore, LAMBDA(s, d, MID(s, 1, IFERROR(FIND(d, s)-LEN(d), LEN(s)))),
        _rightAfter, LAMBDA(s, d, MID(s, IFERROR(FIND(d, s)+LEN(d), 1), LEN(s))),
        LET(
            normalize,
                LAMBDA(dtstr, LET(
                    dt, TRIM(SUBSTITUTE(dtstr, "T", " ")),
                    seq, SUBSTITUTE(dt, " ", ""),
                    IF(_isDigits(seq),
                        IFS(
                            LEN(seq)=14, TEXT(seq, "####-##-## ##!:##!:##"),
                            LEN(seq)=8,  TEXT(seq, "####-##-##"),
                            LEN(seq)=6,  TEXT(seq, "##!:##!:##"),
                            TRUE, dt
                        ),
                        dt
                    )
                )),
            getTimezone,
                LAMBDA(dt, LET(
                    tails, RIGHT(dt, {1;5;6}),
                    patterns, {"Z", "+????", "-????", "+??:00", "-??:00"},
                    CONCAT(XLOOKUP(patterns, tails, tails, "", 2))
                )),
            toLocal,
                LAMBDA(dt, LEFT(dt, LEN(dt) - LEN(getTimezone(dt)))),
            datetime, toLocal(normalize(日時文字列)),
            date, DateValue(_leftBefore(datetime, " ")),
            time, TimeValue(_rightAfter(datetime, " ")),
            date + time
        )
    )
)

【構文】

DATETIMEVALUE(日時文字列)

【引数】

引数	内容
日時文字列	日時で書式化された文字列

【戻り値】

日時文字列をパースした結果のシリアル値。

【説明】

DATETIMEVALUE 関数は、日時書式としてみなされる文字列を、シリアル値に変換します。

日時書式としては、Excel が標準で認識可能なもののほか、ISO 8601 形式と、区切りなしの YYYYMMDD 形式 などに対応します。

日時文字列が日時の書式としてみなせない文字列の場合は #VALUE! エラーとなります。

【使用例】

使用例	結果	コメント
=DATETIMEVALUE("2022/5/1 12:34")	2022/05/01 12:34	Excel 標準
=DATETIMEVALUE( "2022-05-01 12:34:56")	2022/5/1 12:34:56	DB形式
=DATETIMEVALUE( "2022-05-01T12:34:56.789Z")	2022/5/1 12:34:56.789	ISO 8601 のUTC
=DATETIMEVALUE( "2022-05-01T12:34:56.789+09:00")	2022/5/1 12:34:56.789	ISO 8601 のタイムゾーンあり
=DATETIMEVALUE("20220501123456")	2022/5/1 12:34:56	YYYYMMDDhhmmss （14桁数字を日時とみなす）
=DATETIMEVALUE("20220501")	2022/5/1 00:00:00	YYYYMMDD （8桁数字を日付とみなす）
=DATETIMEVALUE("123456")	12:34:56	hhmmss （6桁数字を時刻とみなす）
=DATETIMEVALUE( "令和4年5月1日 12時34分56秒")	2022/5/1 12:34:56	和暦

和暦パース

【名前の定義】

名前
WAREKIVALUE
コメント
和暦文字列をシリアル値に変換します。
参照範囲

=LAMBDA(和暦文字列, IF(和暦文字列="", DATEVALUE(""),
    LET(
        wareki, SUBSTITUTE(和暦文字列, "元", "1"),
        dvs, DATEVALUE(wareki & {"", "1日", ".1", "1月1日", ".1.1"}),
        FILTER(dvs, NOT(ISERROR(dvs)), DATEVALUE(wareki))
    )
))

【構文】

WAREKIVALUE(和暦文字列)

【引数】

引数	内容
和暦文字列	和暦で書式化された文字列

【戻り値】

【説明】

WAREKIVALUE 関数は、既存の DATEVALUE 関数を和暦用に拡張したものです。

既存の DATEVALUE 関数でも和暦をシリアル値に変換できますが、元号の「元年」には今のところ対応していません。 WAREKIVALUE 関数は「元年」を「1年」として変換します。

また、月や日がない、年だけの文字列でもシリアル値に変換できます。その場合は場合は当年1月1日のシリアル値となります。

和暦文字列が日付書式としてみなせない場合は #VALUE! エラーとなります。

【使用例】

使用例	結果	コメント
=WAREKIVALUE("令和4年5月1日")	2022/5/1
=WAREKIVALUE("令和元年5月1日")	2019/5/1	元年表記
=WAREKIVALUE("平成12年")	2000/1/1	年のみから
=WAREKIVALUE("H12")	2000/1/1	年のみから
=WAREKIVALUE("昭和50年3月")	1975/3/1	年月のみから

年度

【名前の定義】

名前
NENDO
コメント
日付が含まれる年度を取得します
参照範囲

=LAMBDA(日付, LET(
    START_MONTH, 4,
    year, EDATE(日付, -START_MONTH + 1),
    if(ISERROR(year),
        year,
        DATE(YEAR(year), START_MONTH, 1)
    )
))

【構文】

NENDO(日付)

【引数】

引数	内容
日付	基準となる日付

【戻り値】

指定の日付が含まれる年度の４月１日のシリアル値。

【説明】

NENDO 関数は指定された日付にとっての、４月始まりの「年度」を表すシリアル値を返します。年度を表すシリアル値は当年の４月１日０時丁度です。

例えば引数の日付が「2022/5/1」では「2022/4/1」を、「2022/3/2」なら「2021/4/1」を返します。

結果のシリアル値から年の数字のみを表示したい場合には、セルの表示形式を変更したり、さらに YEAR 関数を使うなどしてください。

引数が日付の書式としてみなせない文字列の場合は #VALUE! エラーとなります。

【使用例】

使用例	結果	コメント
=NENDO(TODAY())	2022/04/01	今日の年度を取得
=NENDO(DATE(2022, 05, 03))	2022/04/01	シリアル値
=NENDO("2022/03/31")	2021/04/01	日付書式文字列
=YEAR(NENDO("2022/03/31"))	2021	年度のみを取得

第N何曜日

【名前の定義】

名前
NTHWEEKDAY
コメント
当月のN番目の指定曜日の日付を返します
参照範囲

=LAMBDA(日付, 第N, 曜日,
    LET(
        prevEom, EOMONTH(日付, -1),
        nth, 第N,
        wd, IF(ISTEXT(曜日), FIND(LEFT(曜日), "日月火水木金土"), 曜日),
        IF(AND(1<=wd, wd<=7), LET(
                z, WEEKDAY(prevEom),
                dom, wd - z + ((wd<=z) + nth -1)*7,
                prevEom + dom
            ), 
            #VALUE!
        )
    )
)

【構文】

NTHWEEKDAY(当月日付, 第N, 曜日)

【引数】

引数	内容
当月日付	当月に含まれる日付
第N	N番目
曜日	曜日番号か曜日名

【戻り値】

当月のN回目の指定曜日の日付のシリアル値。

【説明】

NTHWEEKDAY 関数は、ある月の「第2月曜日」とか「第3木曜日」等の規則による日付を計算します。

例えば、2022年5月の第2月曜日は「2022/5/9」になります。

月の指定は「当月日付」で行い、これはその月に含まれる日付ならいつでもいいです。

「第N」にはその月の何回目の曜日が欲しいのかを指定します。通常は 1 ～ 5 の整数です。

「曜日」には曜日番号か、曜日名を指定します。曜日番号は一週間を日曜日から「1」始まりで割り振った番号で、土曜日の「7」までになります。それが分かりにくい場合には、"月"や"木曜日"といった曜日名で指定することもできます。

不正な日付や曜日が指定された場合 #VALUE! エラーを返します。

【使用例】

使用例	結果	コメント
=NTHWEEKDAY(TODAY(),1, 3)	2022/5/10	今月の第1火曜日
=NTHWEEKDAY("2022/6/1", 2, "月")	2022/6/13	曜日名を指定

AFE ソース

Excel に Advanced Formula Environment アドインを導入されている上級者のために、本記事掲載の全てのユーザー定義関数を一括登録できるソースを用意しました。

以下のソースを AFE の Editor にコピー＆ペーストしてください。関数名の衝突を避けるため、任意の名前で専用の Namespace を用意することをお勧めします。

// シーゴのExcel研究室
// 【Excel LAMBDA】日付・時刻操作編（その１）【ユーザー定義関数集】
// https://www.shegolab.jp/entry/excel-lambda-datetime-01

//----------------------------------------------------------
// 変換
//----------------------------------------------------------

/**
 * 日付のみのシリアル値
 */
TODATE
=LAMBDA(シリアル値, INT(シリアル値))
;


/**
 * 時刻のみのシリアル値
 */
TOTIME
=LAMBDA(シリアル値, MOD(シリアル値, 1))
;


/**
 * 時間換算
 */
TOHOURS
=LAMBDA(シリアル値, シリアル値 * 24)
;


//-------------------------------------------------------------
// 日時書式
//-------------------------------------------------------------


/**
 * 漢数字の和暦
 */
KANJIWAREKI
=LAMBDA(日付, LET(
    wareki, TEXT(INT(日付), "[DBNum1]ggge年m月d日"),
    isGannen, IFERROR(SEARCH("??一年", wareki)=1, FALSE),
    IF(isGannen,
        REPLACE(wareki, 3, 1, "元"),
        wareki
    )
))
;
// - 「元年」表記に対応する


/**
 * 日時パース
 */
DATETIMEVALUE
=LAMBDA(日時文字列,
    LET(
        _isDigits, LAMBDA(s, NOT(OR(ISERROR(MID(s, SEQUENCE(LEN(s)), 1)+0)))),
        _leftBefore, LAMBDA(s, d, MID(s, 1, IFERROR(FIND(d, s)-LEN(d), LEN(s)))),
        _rightAfter, LAMBDA(s, d, MID(s, IFERROR(FIND(d, s)+LEN(d), 1), LEN(s))),
        LET(
            normalize,
                LAMBDA(dtstr, LET(
                    dt, TRIM(SUBSTITUTE(dtstr, "T", " ")),
                    seq, SUBSTITUTE(dt, " ", ""),
                    IF(_isDigits(seq),
                        IFS(
                            LEN(seq)=14, TEXT(seq, "####-##-## ##!:##!:##"),
                            LEN(seq)=8,  TEXT(seq, "####-##-##"),
                            LEN(seq)=6,  TEXT(seq, "##!:##!:##"),
                            TRUE, dt
                        ),
                        dt
                    )
                )),
            getTimezone,
                LAMBDA(dt, LET(
                    tails, RIGHT(dt, {1;5;6}),
                    patterns, {"Z", "+????", "-????", "+??:00", "-??:00"},
                    CONCAT(XLOOKUP(patterns, tails, tails, "", 2))
                )),
            toLocal,
                LAMBDA(dt, LEFT(dt, LEN(dt) - LEN(getTimezone(dt)))),
            datetime, toLocal(normalize(日時文字列)),
            date, DateValue(_leftBefore(datetime, " ")),
            time, TimeValue(_rightAfter(datetime, " ")),
            date + time
        )
    )
)
;
// - ISO 8601 書式をサポート
// - YYYYMMDD、YYYYMMDDHHMMSS、HHMMSSをサポート

/**
 * 和暦パース
 */
WAREKIVALUE
=LAMBDA(和暦文字列, IF(和暦文字列="", DATEVALUE(""),
    LET(
        wareki, SUBSTITUTE(和暦文字列, "元", "1"),
        dvs, DATEVALUE(wareki & {"", "1日", ".1", "1月1日", ".1.1"}),
        FILTER(dvs, NOT(ISERROR(dvs)), DATEVALUE(wareki))
    )
))
;
// - 「元年」表記に対応する
// - 年のみでもOK


//-------------------------------------------------------------
// 日時計算
//-------------------------------------------------------------

/**
 * 年度
 */
NENDO
=LAMBDA(日付, LET(
    START_MONTH, 4,
    year, EDATE(日付, -START_MONTH + 1),
    if(ISERROR(year),
        year,
        DATE(YEAR(year), START_MONTH, 1)
    )
))
;

/**
 * 当月第N何曜日
 */
NTHWEEKDAY
=LAMBDA(当月基準日, 第N, 曜日,
    LET(
        prevEom, EOMONTH(当月基準日, -1),
        nth, 第N,
        wd, IF(ISTEXT(曜日), FIND(LEFT(曜日), "日月火水木金土"), 曜日),
        IF(AND(1<=wd, wd<=7), LET(
                z, WEEKDAY(prevEom),
                dom, wd - z + ((wd<=z) + nth -1)*7,
                prevEom + dom
            ), 
            #VALUE!
        )
    )
)
;

// EOF

www.shegolab.jp

変更履歴

[2022-06-12] 公開

シーゴの Excel 研究室

タイトル変更しました（旧称：今日を乗り切るExcel研究所）

【Excel LAMBDA】日付・時刻操作編（その１）【ユーザー定義関数集】

日付・時刻操作関数集その１

日付のみ

【解説】シリアル値とは

時刻のみ

時間換算

漢数字の和暦に書式化

日時文字列 ⇒ シリアル値

和暦パース

年度

第N何曜日

AFE ソース

関連記事

変更履歴

日付・時刻操作関数集 その１

日付のみ

【解説】シリアル値とは

時刻のみ

時間換算

漢数字の和暦に書式化

日時文字列 ⇒ シリアル値

和暦パース

年度

第N何曜日

AFE ソース

関連記事

変更履歴

日付・時刻操作関数集その１