エクセルで日付変換を自在に行なう関数を作成
概要
生産管理システム等で、指定納期日からリードタイムや作業日数を考慮して手配開始日を求めたいとき、VBAを用いなければExcelに用意されている日付関数を用いた数式を作成します。
ところが、Excelに用意されている関数で行えるのは、「稼働日〇日前」「カレンダー△日前」といった１回の変換であり、例えば「稼働日〇日前→カレンダー△日前」といった複数の変換を行なうには、日付関数を入れ子にした数式を作成する必要があります。
本稿では、こうした需要に対応する、自在に日付を変換する数式をあらかじめ定義して、数式内で拡張した関数のように利用する方法を紹介します。
（本稿で紹介する方法は、Excel365が必要です。それ以前のバージョンに対応する方法もございますので、お問い合わせください。）
初稿 2024年9月5日
改訂 2024年9月9日 数式修正 2024年9月27日
例題
「指定納期日」から、「途中１」,「途中２」の工程をたどって、「手配開始日Ａ」を求める場合を想定します。
指定納期日 → 途中１ → 途中２ → 手配開始日A
稼働日２日前 カレンダー２日前 稼働日３日前
例として「指定納期日」が［ 2024/9/25 ］の場合、「手配開始日A」を求めるための具体的な変換は下記のようになります。
指定納期日 ［ 2024/9/25（水） ］
↓ 稼働日２日前 土日、秋分の日（9/23）除く
途中１ ［ 2024/9/20（金） ］
↓ カレンダー２日前
途中２ ［ 2024/9/18（水） ］
↓ 稼働日3日前 土日、敬老の日（9/16）除く
手配開始日A ［ 2024/9/12（木） ］
Excelファイルをダウンロード
 | 納期日変換紹介 (2024-09-27 ・ 26KB) 例題で紹介したシート、定義、数式を含んだExcelファイルをご用意いたしましたので、こちらをダウンロードして下さい。 以下では、シート及び数式を空のブックから作成する手順を説明します。 |
変換に用いるパラメータを用意
シート名『納期日変換』のシートを追加します。 （『納期日変換』は固定）
Ａ列１行～５行に、左記の文字を入力します。
１行～５行の書式を＜文字列＞に設定します。
（ダウンロードファイルには、２行と４行に運用のガイドのためのコメントがついています。）
休日設定のテーブルを用意
シート名『休日設定』のシートを追加します。
テーブル名『tbl_休日設定』で、テーブルを作成します。 （『tbl_休日設定』は固定）
テーブルの項目名「日本の休日」として、祭日、事業所の休日等を登録します。
・項目名は、『納期日変換』シートで使用しますので、変えてもかまいません。
・納期変換のパターンにより、項目を追加して何種類かの休日を登録できます。
・ダウンロードファイルでは、春分の日、秋分の日以外の毎年決まった祭日は
数式で自動設定できるようになっています。
この数式の解説は割愛いたしますが、お問い合わせいただければ解説いたします。
［ 工程 ］～［ 次の工程 ］を入力
シート名「納期日変換」に戻ります。
「途中１」「途中２」「手配開始日Ａ」を、図のように設定します。
工程 | 途中１ | 途中２ | 手配開始日Ａ |
±シフト日数 | -2 | -2 | -3 |
休日設定 | 日本の休日 | 日本の休日 | |
週末指定 | 1 | 1 | |
次の工程 | 指定納期日 | 途中１ | 途中２ |
指定納期日 → 途中１
稼働日２日前
この設定をＢ列に登録します。
１行に「途中１」と入力します。
２行に「-2」と入力します。
３行に「日本の休日」と入力します。
４行に「1」と入力します。
５行に「指定納期日」と入力します。
途中１ → 途中２
カレンダー２日前
この設定をＣ列に登録します。
１行に「途中２」と入力します。
２行に「-2」と入力します。
３行、４行はブランクのままにします。
５行に「途中１」と入力します。
途中２ → 手配開始日A
稼働日３日前
この設定をＤ列に登録します。
１行に「手配開始日Ａ」と入力します。
２行に「-3」と入力します。
３行に「日本の休日」と入力します。
４行に「1」と入力します。
５行に「途中２」と入力します。
ここまでで、「指定納期日」～「手配開始日Ａ」 までの工程の繋がりが設定できました。
これを図で表現すると、左のようになります。
各項目の説明です。
１行［ 工程 ］ ・重複しない工程名を入力します。
２行［ ±シフト日数 ］
・変換する日数を入力します。
・例えば［ 工程 ］が［ 次の工程 ］の２日前の場合、「-2」と入力します。
・先頭の「+」「－」は省略できません。
（「-0」と「+0」を区別しているためです。）
・全角でも半角でも入力できます。
・ブランクの場合、「-0」とみなします。
３行［ 休日設定 ］
・稼働日に含めない休日を指定します。
・「カレンダー△日前」の場合は、指定しません。
・『tbl_休日設定』の項目名を入力します。
・ダウンロードファイルでは、テーブルの見出しを利用したドロップダウンリストで入力できるようになっています。
４行［ 週末指定 ］
・稼働日に含めない週末を指定します。
・「カレンダー△日前」の場合は、指定しません。
・全角でも半角でも入力できます。
・休日とする週末の曜日を、ExcelのWORKDAY.INTL関数に準拠して設定します。
１ ： 土曜と日曜
（WORKDAY.INTL関数では、省略した場合”1”とみなされますが、
この数式ではブランクは”休日なし”として扱います。）
２ ： 日曜と月曜
３ ： 月曜と火曜
４ ： 火曜と水曜
５ ： 水曜と木曜
６ ： 木曜と金曜
７ ： 金曜と土曜
１１： 日曜のみ
１２： 月曜のみ
１３： 火曜のみ
１４： 水曜のみ
１５： 木曜のみ
１６： 金曜のみ
１７： 土曜のみ
0と1の7桁： 月曜～日曜の順で指定（ 1 が休日）
（例えば月水木が休日の場合 ”1011000” と指定します。）
５行［ 次の工程 ］
・その［ 工程 ］のひとつ後の工程の名称を入力します。
・［ 次の工程 ］をたどっていくと、「指定納期日」に行きつきます。
（例題では、「手配開始日Ａ」→「途中２」→「途中１」→「指定納期日」となります。）
・［ 次の工程 ］に入力する［ 工程 ］は、左側の列の［ 工程 ］しか入力できません。
（循環参照を検出するための制約です。）
・ブランクの場合、「指定納期日」とみなします。
「指定日変換」を【名前の定義】に登録
以下の数式を【名前の定義】に登録します。 2024/9/27改訂
=LAMBDA(変換前日付,[指定工程], LET( loop1,LAMBDA(self,工程, LET( 工程列,IF(COUNTIF(納期日変換!$1:$1,工程)<>1,"x1", MATCH(工程,納期日変換!$1:$1,0) ), 次の工程入力,TRIM(INDEX(納期日変換!$5:$5,,工程列)), 次の工程,IF(次の工程入力 = "","指定納期日",次の工程入力), 次の工程列,IF(次の工程 = "指定納期日",0, IFNA(MATCH(次の工程,納期日変換!$1:$1,0),"x2") ), com," // 「次の工程」は左側の「工程」を指定する（循環参照の簡易的な検知のため） // ", IF(工程列 = "x1","x1", IF(次の工程列 = "x2","x2", IF(次の工程 = "指定納期日", 工程列, IF(次の工程列 >= 工程列,"x3", HSTACK(工程列,self(self,次の工程)) ) ) ) ) ) ), 工程リンク,loop1(loop1,指定工程), リンク件数,COLUMNS(工程リンク), 日付変換, LAMBDA(工程列,直前日付, LET( シフト日数入力,ASC(INDEX(納期日変換!$2:$2,,工程列)), 休日設定入力,INDEX(納期日変換!$3:$3,,工程列), 週末指定入力,ASC(INDEX(納期日変換!$4:$4,,工程列)), シフト日数,IF(シフト日数入力 = "","-0",シフト日数入力), 正負値,IF(ISERR(FIND(LEFT(シフト日数,1),"+-")),"x5", IF(NOT(ISNUMBER(VALUE(シフト日数))),"x5", IF(LEFT(シフト日数,1)="+",1,-1))), 絶対値,ABS(シフト日数), 休日列,IF(休日設定入力="","",IFNA(MATCH(休日設定入力,tbl_休日設定[#見出し],0),"x6")), 休日集合,CHOOSECOLS(tbl_休日設定,休日列), 週末指定,IFS(週末指定入力="","0000000", 週末指定入力="1111111","x7", ISERR(VALUE(週末指定入力)),"x7", NOT(ISNA(MATCH(VALUE(週末指定入力),{1,2,3,4,5,6,7,11,12,13,14,15,16,17},0))),VALUE(週末指定入力), SUBSTITUTE(週末指定入力,"1","0")="0000000",週末指定入力, TRUE,"x7" ), 変換A,WORKDAY.INTL(直前日付-正負値,正負値,週末指定,IF(休日列="",,休日集合)), 変換B,WORKDAY.INTL(直前日付,正負値*絶対値,週末指定,IF(休日列="",,休日集合)), IF(正負値 = "x5","x5", IF(休日列 = "x6","x6", IF(週末指定 = "x7","x7", IF(絶対値 = 0,変換A,変換B) ) ) ) ) ), loop2,LAMBDA(self,リンク列,直前日付, LET( 変換後日付,日付変換(INDEX(工程リンク,,リンク列),直前日付), IF(ISTEXT(変換後日付),変換後日付, IF(リンク列 > 1, self(self,リンク列 - 1,変換後日付), 変換後日付 ) ) ) ), 日付表示,loop2(loop2,リンク件数,変換前日付), エラー表示,IF(ISTEXT(変換前日付),"x4", IF(ISNA(MATCH("x?",工程リンク,0)) = FALSE, INDEX(工程リンク,,リンク件数), IF(ISTEXT(日付表示),日付表示,"") ) ), IF(ISBLANK(変換前日付),"", IF(OR(指定工程 = {"指定納期日",""},ISOMITTED(指定工程)), 変換前日付, IF(エラー表示 = "", 日付表示, エラー表示 ) ) ) ) )
具体的な手順
１．上記数式をクリップボードにコピー
２．エクセル画面 ［数式］タブ → ［名前の定義］クリック
（左の状態になります）
３．〈名前〉に「指定日変換」と入力
４．Tabキーで〈参照範囲〉に移動して、クリップボードを貼付け（マウスの右クリック）
５．〈OK〉クリック
（名前に「指定日変換」という数式が登録されます）
６．〈閉じる〉クリック
なお、見やすい状態にした数式は、こちらです。 2024/9/27改訂
=LAMBDA(変換前日付,[指定工程],
LET(
loop1,LAMBDA(self,工程,
LET(
工程列,IF(COUNTIF(納期日変換!$1:$1,工程)<>1,"x1",
MATCH(工程,納期日変換!$1:$1,0)
),
次の工程入力,TRIM(INDEX(納期日変換!$5:$5,,工程列)),
次の工程,IF(次の工程入力 = "","指定納期日",次の工程入力),
次の工程列,IF(次の工程 = "指定納期日",0,
IFNA(MATCH(次の工程,納期日変換!$1:$1,0),"x2")
),
com," // 「次の工程」は左側の「工程」を指定する（循環参照の簡易的な検知のため） // ",
IF(工程列 = "x1","x1",
IF(次の工程列 = "x2","x2",
IF(次の工程 = "指定納期日",
工程列,
IF(次の工程列 >= 工程列,"x3",
HSTACK(工程列,self(self,次の工程))
)
)
)
)
)
),
工程リンク,loop1(loop1,指定工程),
リンク件数,COLUMNS(工程リンク),
日付変換,
LAMBDA(工程列,直前日付,
LET(
シフト日数入力,ASC(INDEX(納期日変換!$2:$2,,工程列)),
休日設定入力,INDEX(納期日変換!$3:$3,,工程列),
週末指定入力,ASC(INDEX(納期日変換!$4:$4,,工程列)),
シフト日数,IF(シフト日数入力 = "","-0",シフト日数入力),
正負値,IF(ISERR(FIND(LEFT(シフト日数,1),"+-")),"x5",
IF(NOT(ISNUMBER(VALUE(シフト日数))),"x5",
IF(LEFT(シフト日数,1)="+",1,-1))),
絶対値,ABS(シフト日数),
休日列,IF(休日設定入力="","",IFNA(MATCH(休日設定入力,tbl_休日設定[#見出し],0),"x6")),
休日集合,CHOOSECOLS(tbl_休日設定,休日列),
週末指定,IFS(週末指定入力="","0000000",
LET(
loop1,LAMBDA(self,工程,
LET(
工程列,IF(COUNTIF(納期日変換!$1:$1,工程)<>1,"x1",
MATCH(工程,納期日変換!$1:$1,0)
),
次の工程入力,TRIM(INDEX(納期日変換!$5:$5,,工程列)),
次の工程,IF(次の工程入力 = "","指定納期日",次の工程入力),
次の工程列,IF(次の工程 = "指定納期日",0,
IFNA(MATCH(次の工程,納期日変換!$1:$1,0),"x2")
),
com," // 「次の工程」は左側の「工程」を指定する（循環参照の簡易的な検知のため） // ",
IF(工程列 = "x1","x1",
IF(次の工程列 = "x2","x2",
IF(次の工程 = "指定納期日",
工程列,
IF(次の工程列 >= 工程列,"x3",
HSTACK(工程列,self(self,次の工程))
)
)
)
)
)
),
工程リンク,loop1(loop1,指定工程),
リンク件数,COLUMNS(工程リンク),
日付変換,
LAMBDA(工程列,直前日付,
LET(
シフト日数入力,ASC(INDEX(納期日変換!$2:$2,,工程列)),
休日設定入力,INDEX(納期日変換!$3:$3,,工程列),
週末指定入力,ASC(INDEX(納期日変換!$4:$4,,工程列)),
シフト日数,IF(シフト日数入力 = "","-0",シフト日数入力),
正負値,IF(ISERR(FIND(LEFT(シフト日数,1),"+-")),"x5",
IF(NOT(ISNUMBER(VALUE(シフト日数))),"x5",
IF(LEFT(シフト日数,1)="+",1,-1))),
絶対値,ABS(シフト日数),
休日列,IF(休日設定入力="","",IFNA(MATCH(休日設定入力,tbl_休日設定[#見出し],0),"x6")),
休日集合,CHOOSECOLS(tbl_休日設定,休日列),
週末指定,IFS(週末指定入力="","0000000",
週末指定入力="1111111","x7",
ISERR(VALUE(週末指定入力)),"x7",
NOT(ISNA(MATCH(VALUE(週末指定入力),{1,2,3,4,5,6,7,11,12,13,14,15,16,17},0))),VALUE(週末指定入力),
SUBSTITUTE(週末指定入力,"1","0")="0000000",週末指定入力,
TRUE,"x7"
),
変換A,WORKDAY.INTL(直前日付-正負値,正負値,週末指定,IF(休日列="",,休日集合)),
変換B,WORKDAY.INTL(直前日付,正負値*絶対値,週末指定,IF(休日列="",,休日集合)),
IF(正負値 = "x5","x5",
IF(休日列 = "x6","x6",
IF(週末指定 = "x7","x7",
IF(絶対値 = 0,変換A,変換B)
)
)
)
)
),
loop2,LAMBDA(self,リンク列,直前日付,
LET(
変換後日付,日付変換(INDEX(工程リンク,,リンク列),直前日付),
IF(ISTEXT(変換後日付),変換後日付,
IF(リンク列 > 1,
self(self,リンク列 - 1,変換後日付),
変換後日付
)
)
)
),
日付表示,loop2(loop2,リンク件数,変換前日付),
エラー表示,IF(ISTEXT(変換前日付),"x4",
IF(ISNA(MATCH("x?",工程リンク,0)) = FALSE,
INDEX(工程リンク,,リンク件数),
IF(ISTEXT(日付表示),日付表示,"")
)
),
IF(ISBLANK(変換前日付),"",
IF(OR(指定工程 = {"指定納期日",""},ISOMITTED(指定工程)),
変換前日付,
IF(エラー表示 = "",
日付表示,
エラー表示
)
)
)
)
)
ISERR(VALUE(週末指定入力)),"x7",
NOT(ISNA(MATCH(VALUE(週末指定入力),{1,2,3,4,5,6,7,11,12,13,14,15,16,17},0))),VALUE(週末指定入力),
SUBSTITUTE(週末指定入力,"1","0")="0000000",週末指定入力,
TRUE,"x7"
),
変換A,WORKDAY.INTL(直前日付-正負値,正負値,週末指定,IF(休日列="",,休日集合)),
変換B,WORKDAY.INTL(直前日付,正負値*絶対値,週末指定,IF(休日列="",,休日集合)),
IF(正負値 = "x5","x5",
IF(休日列 = "x6","x6",
IF(週末指定 = "x7","x7",
IF(絶対値 = 0,変換A,変換B)
)
)
)
)
),
loop2,LAMBDA(self,リンク列,直前日付,
LET(
変換後日付,日付変換(INDEX(工程リンク,,リンク列),直前日付),
IF(ISTEXT(変換後日付),変換後日付,
IF(リンク列 > 1,
self(self,リンク列 - 1,変換後日付),
変換後日付
)
)
)
),
日付表示,loop2(loop2,リンク件数,変換前日付),
エラー表示,IF(ISTEXT(変換前日付),"x4",
IF(ISNA(MATCH("x?",工程リンク,0)) = FALSE,
INDEX(工程リンク,,リンク件数),
IF(ISTEXT(日付表示),日付表示,"")
)
),
IF(ISBLANK(変換前日付),"",
IF(OR(指定工程 = {"指定納期日",""},ISOMITTED(指定工程)),
変換前日付,
IF(エラー表示 = "",
日付表示,
エラー表示
)
)
)
)
)
文字数制限のためか、このままでは名前の定義の〈参照範囲〉に登録できなかったので、余分な空白を除去した数式を登録するようにしました。
「指定日変換」を確認
数式が正しく登録されたかテストします。
どこかのセルに、書式指定を＜日付＞に設定してから、
=指定日変換("2024/9/25"+0,"手配開始日Ａ") と入力して下さい。
最初の引数で“+0”とするのは、文字列型表現の日付のデータ型を強制的に数値にするためです。
=指定日変換(DATEVALUE("2024/9/25"),"手配開始日Ａ") でもOKです。
通常の運用では、引数に日付を直接書き込むことはあまりなく、
指定納期日が表示されたセルを参照すると思われます。
数式を入力したセルに"2024/9/12" と表示されれば、正しく登録されています。
前述のように、「手配開始日Ａ」は「指定納期日」の ”2024/9/25” から下記の変換を行って算出されています。
指定納期日 ［ 2024/9/25（水） ］
↓ 稼働日２日前 土日、秋分の日（9/23）除く
途中１ ［ 2024/9/20（金） ］
↓ カレンダー２日前
途中２ ［ 2024/9/18（水） ］
↓ 稼働日3日前 土日、敬老の日（9/16）除く
手配開始日A ［ 2024/9/12（木） ］
引数の説明
関数の引数について、説明します。
＝指定日変換（ 指定納期日 , 工程 ）
指定納期日 ： 指定納期日を日付型で設定します。
直接入力でも可能ですが、通常は他のセルの日付データを参照すると思われます。
工程 ： 「納期日変換」シート１行目の［ 工程 ］を設定します。
“指定納期日” と設定、または設定を省略すると、指定納期日の引数をそのまま返します。
別の工程を設定
下記のような別の工程「手配開始日Ｂ」を設定してみます。
指定納期日 ［ 2024/9/25（水） ］
↓ 稼働日２日前 土日、秋分の日（9/23）除く
途中１ ［ 2024/9/20（金） ］
↓ カレンダー4日前
途中３ ［ 2024/9/16（月・祝） ］
↓ 稼働日3日前 土日、敬老の日（9/16）除く
途中４ ［ 2024/9/11（水） ］
↓ 「棚卸し」避ける 棚卸し 9/10,9/11
手配開始日Ｂ ［ 2024/9/9（月） ］
「途中１」までは、「手配開始日Ａ」を流用しています。
シート名「休日設定」を選択します。
『tbl_休日設定』に、「棚卸し」の項目を追加します。
「棚卸し」の列に、“2024/9/10”、“2024/9/11”を入力します。
シート名「納期日変換」に戻ります。
「途中３」「途中４」「手配開始日Ｂ」を、図のように設定します。
工程 | 途中３ | 途中４ | 手配開始日Ｂ |
±シフト日数 | -4 | -3 | -0 |
休日設定 | 日本の休日 | 棚卸し | |
週末指定 | 1 | ||
次の工程 | 途中１ | 途中３ | 途中４ |
「指定納期日」～「手配開始日Ｂ」の工程の繋がりを図で表現すると、
左のようになります。
（テストの結果表示は、省略します。）
特定の曜日を避ける設定
例えば「火曜日ならば１日前の月曜日」「木金ならばその週の土曜日」のような設定ができます。
工程名「火→月曜日」
指定納期日 ［ 2024/9/24（火） ］
↓
火→月曜日 ［ 2024/9/23（月） ］
工程名「木金→土曜日」
指定納期日 ［ 2024/9/19（木） ］ ［ 2024/9/20（金） ］
↓ ↓
木金→土曜日 ［ 2024/9/21（土） ］ ［ 2024/9/21（土） ］
「火→月曜日」「木金→土曜日」を、図のように設定します。
工程 | 火→月曜日 | 木金→土曜日 |
±シフト日数 | -0 | +0 |
休日設定 | ||
週末指定 | 0100000 | 0001100 |
次の工程 | 指定納期日 | 指定納期日 |
［ ±シフト日数 ］の設定は、
「火→月曜日」の場合、前の日付を求めるので「-0」、
「木金→土曜日」の場合、後の日付を求めるので「+0」と指定します。
［ 週末設定 ］の設定は、
「火→月曜日」の場合、火曜日だけ週末とするので「0100000」、
「木金→土曜日」の場合、木曜と金曜日だけ週末とするので「0001100」と指定します。
テストしてみると、
”2024/9/24”（火）の「火→月曜日」では”2024/9/23”（月）が、
”2024/9/19”（木）と”2024/9/20”（金）の「木金→土曜日」では”2024/9/21”（土）が表示されています。
他の曜日の日付を入力すると、そのままの日付が表示されます。
日付を一覧表示させて確認する
ここまでの説明では、設定が正しいかどうかを、セルに数式を入力してひとつずつ確認していました。
次に、連続した日付を「指定納期日」として用意して 、全ての工程の出力結果をまとめて表示させる方法を紹介します。
手順１
「納期日変換」シートの６行以下の１０～３０行分くらいの行の書式を日付にします。
例では７行目から１５行分を設定します。
手順２
A列の先頭行に =SEQUENCE(15,,TODAY()-3,1) と入力します。
“15” は出力する行数、“TODAY()-3” は今日の３日前の日付からスタート、“1” は１日ごと（毎日）という意味です。
手順３
Ｂ列の先頭行に =指定日変換($A7,B$1) と入力します。
“$” を指定の位置につけて下さい。
手順４
手順３のセルを工程がある列の１５行分の範囲にコピーします。
このような状態になります。
例えば 指定納期日が「2024/9/3」 で 工程が「手配開始日Ａ」の場合、
関数は「2024/8/23」を返すことがわかります。
途中の過程も含めて、想定通りに変換されているかをチェックすることができます。
エラー表示
数式の戻り値には、以下のエラー表示が用意されています。
エラー表示 | エラーの意味 |
x1 | 「納期日変換」シート１行目に引数の［ 工程 ］が見つからない、または重複している (*) |
x2 | 「納期日変換」シート５行目の［ 次の工程 ］が１行目［ 工程 ］に見つからない |
x3 | ［ 次の工程 ］が指定する［ 工程 ］が、［ 次の工程 ］より右側の列にある |
x4 | 引数の［ 指定納期日 ］が、日付型（数値型）ではない (*) |
x5 | 「納期日変換」シート２行目の［ ±シフト日数 ］に誤りがある |
x6 | 「納期日変換」シート３行目の［ 休日設定 ］の項目が「tbl_休日設定」に見つからない |
x7 | 「納期日変換」シート４行目の［ 週末指定 ］に誤りがある |
上記以外の想定していないエラーが発生した場合は、Excelのエラーメッセージが出ます。 (*) 2024/9/9改訂
仕様の変更、問い合わせ、ご相談
今回ご紹介した例で、仕様を変えてお使いになりたい場合は、ご相談いただければ検討いたします。
また、納期日等の日付変換のシステム作成に関してお問い合わせやご相談がありましたら、ご連絡ください。
なお、本稿で紹介した数式等をお使いの結果生じた損害について、当社は責任を負いません。
（株）ソアラ・サプライ 担当：入沢 電話 (03)3763-4331
