休暇を引いた日付を表示させたい。

質問

例1）
今日の10/10からカレンダーの休日を引いた３日前の日付を表示させたい。

例2）(8)の日は休日
10/10日　10/9　10/(8)　10/7　10/6　10/5　　表示を10/6　

エクセルでやりたいのですが？
出来たら関数を使って。またはVBAの場合はサンプルを付けて詳しくお願いしたいのですが？

勝手な相談で済みませんがよろしくお願いします。

回答No.4

Nii · ベストアンサー

とりあえず、私の例は、金融機関の7営業日前等を求めたくて作成した分です。
このままで動作確認する方法ですが、
エクセルで、【ツール】→【マクロ】→【Visial Basic Editor】で新規ウィンドウが開くと思います。
新規のウィンドウで、【挿入】→【標準モジュール】で出来たサブウィンドウに#3の"Public Function 結果は(処理日 As Date,日前 as Integer) As Date"の行から最終行までを貼り付けて下さい。
これで、【fx】ボタンで表示される関数の貼り付けの中に、結果は・銀行休業日・祝日・第一月曜日の4つの関数がユーザー定義として表示され利用できるようになると思います。

各関数で行なっている内容としては、
結果は・・・与えられた日付から銀行の休業日を除く与えられた何日前かの日付を返す。
例：A1のセルに2002/11/5、B1のセルに=結果は(A1,3)とした場合、B1のセルには、2002/10/30と表示される。（書式はyyyy/mm/ddと設定）

銀行休業日・・・与えられた日付が銀行の休みかどうかを返す。
休業日は、土・日曜日・月曜日は、当日か前日（日曜日）が祝日の場合・他の曜日は当日が祝日の場合、12月31日・1月2日・1月3日とする。

祝日・・・与えられた日付が祝日かどうかを返す。
祝日とは、1月1日・2月11日・4月29日・5月3日・5月4日・5月5日・ 7月20日・9月15日・11月3日・11月23日・12月23日・春分の日・秋分の日・成人の日・体育の日とする。
春分の日・秋分の日は、変動するので、一定の公式をもって導きだす。（ここは説明すると長くなるので省略）
成人の日・体育の日は、2000年までは、固定でしたが、現在は、1月と10月の第一月曜なので、その都度計算する。

第一月曜・・・与えられた日付の最初の月曜日を返す。

そんな所ですか？ちょっとわかりにくい説明になってしまいました。すみません。

回答No.6

mayurin

こんにちは。実は私もこの前似たようなことをやりました。
アドインで分析ツールを組み込んで、セルに以下のように
入力すればＯＫだと思います。
（ヘルプでWORKDAYを検索してみたら詳しく出てきます）

=WORKDAY("02/10/10",-3)
これで2002年10月10日の稼働日で３日前が返されます。

もし、他に休日扱いした日があれば、
=WORKDAY("02/10/10",03,{"2002/10/09","2002/10/08"})
というように指定できます。（例では10月9日と8日を休日扱いにする）

でも、質問中の例2)のように、休日を休日扱いしないということは
できなかったと思います。（10/6は日曜日ですよね？）

回答No.5

taisuke555

私もVBAで作ってみました
　　Sheet1と祝日というシートを使用します

（１）シート名「祝日」をつくりA列に日曜日以外の休日を入力
　　　下のデータは、実際にhttp://www.benri.com/calendar/2002.htmlで拾いました。
　　　2002年～2004年まであります。（それ以降は手入力して下さい）

　　　＃３さんのように祝日を自動で算出しようとも思いましたが、
　　　調べていたら２００３年から「海の日」と「敬老の日」が第3月曜日になるそうで
　　　この分だと他も変わるかな？と思い祝日シートを作成しました。

'02/01/01 02/01/14 02/02/11 02/03/21 02/04/29 02/05/03 02/05/04 02/05/06 02/07/20 02/09/16 02/09/23 02/10/14 02/11/04 02/12/23 03/01/01 03/01/13 03/02/11 03/03/21 03/04/29 03/05/03 03/05/05 03/07/21 03/09/15 03/09/23 03/10/13 03/11/03 03/11/24 03/12/23 04/01/01 04/01/12 04/02/11 04/03/20 04/04/29 04/05/03 04/05/04 04/05/05 04/07/19 04/09/20 04/09/23 04/10/11 04/11/03 04/11/23 04/12/23
　　　１．上記日付をコピー貼り付け
　　　２．データ→区切り位置→カンマやタブなどの・・・をチェック→スペースをチェック→完了
　　　３．横に並んでいますので全て選択→コピー
　　　４．A1セルで形式を選択して貼り付け→行列を入れ替えるをチェック→OK
　　　で縦に並びます
（２）VBAを貼り付ける
　　　１、[Alt]+[F11]を押す。VisialBasicEditerに変更されます。
　　　２、左上にプロジェクトと書いてある中に、
　　　　　　VBAProject(現在のブック名）
　　　　　　　　└Microsoft Excel Objects
　　　　　　　　　　　├Sheet1
　　　　　　　　　　　　・・・
　　　　　　　　　　　└ThisWorkbook
　　　　　とあると思うので、Sheet1をダブルクリック
　　　３．以下のコードを貼り付ける
*********************************この下から*********************************
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If (.Column = 1 And .Row > 1) Then 'A列2行目以降ならば
If (IsDate(.Value)) Then '日付ならば
w_date = .Value '日付をセットする
CNT = 0
'日付を１日づつ前に戻し、土日、祝日かチェックし休日でなければCntを加算する
'cnt = 3（土日、祝日を除いた日が３日前になったら処理を終わる）
Do Until (CNT = 3)
w_date = w_date - 1
'休日のチェック（土曜日は休日に入れない場合 6→7に変更する
If (Weekday(w_date, vbMonday) < 6) Then '土、日でなければ
CNT = CNT + 1 'カウントの加算
'祝日のチェック
With Worksheets("祝日")
'シート名［祝日］のA列の入力が入っている行を最後まで検索し
'w_date が祝日と同じ日かチェックする
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If (w_date = .Cells(i, 1)) Then
CNT = CNT - 1 '祝日の場合Cntを減算する
Exit For
End If
Next i
End With
End If
Loop
Cells(.Row, 2) = DateValue(w_date) '入力行のB列に日付を表示
Else
Cells(.Row, 2) = "***" '入力行のB列にエラー表示
End If
End If
End With
End Sub
*********************************この上まで*********************************
　　　４．もし土曜日は休日でないならば、
　　　　　'休日のチェック（土曜日は休日に入れない場合 6→7に変更する
　　　　　の下の
　　　　　If (Weekday(w_date, vbMonday) < 6) Then '土、日でなければ
　　　　　を
　　　　　If (Weekday(w_date, vbMonday) < 7) Then '日でなければ
　　　　　に変更してください。
（３）[Alt]+[F11]でExcelに戻り
　　　Sheet1のA2セルに[2002/12/10]と入力すると
　　　B2セルに[2002/12/5]（（２）の４で土曜日の処理を変更すれば[2002/12/6]）
　　　と表示されます。

なるべくプログラムにコメントをいれて修正しやすくしていますが、
わからない所などありましたら、補足してください。
長くなってしまいすみません。

回答No.3

Nii

10月の例を示して頂いていますが、2002年の10月8日がカレンダーの休日というのも変なので、12月という事ですよね？

・土曜日・日曜日・祝日を休みとカウント
・祝日が日曜日と重なった場合は、月曜日を振替休日とする。
で以前に作成した分です。
このままでは使用できないと思いますが参考にして下さい。

A1のセルから3日前を表示させたい場合は、=結果は(A1)で、セルの書式を日付にすればOKです。

Public Function 結果は(処理日 As Date,日前 as Integer) As Date
Dim CNT As Integer

結果は = 処理日
Do While CNT < 日前
結果は = 結果は - 1
If Not 銀行休業日(結果は) Then
CNT = CNT + 1
End If
Loop
End Function

Public Function 銀行休業日(今日 As Date) As Integer
Dim 曜日 As Integer

曜日 = Weekday(今日)

Select Case 曜日
Case 1, 7
銀行休業日 = -1
Case 2
If 祝日(今日) Or 祝日(今日 - 1) Then
銀行休業日 = -1
Else
銀行休業日 = 0
End If
Case Else
If 祝日(今日) Then
銀行休業日 = -1
Else
銀行休業日 = 0
End If
End Select

Select Case Format(今日, "mmdd")
Case 102, 103, 1231
銀行休業日 = -1
End Select
End Function

Public Function 祝日(今日 As Date) As Integer
Dim 年 As Long
Dim 春分の日, 秋分の日, 成人の日, 体育の日 As Date

年 = Year(今日)

If 年 >= 2000 Then
成人の日 = 第一月曜(DateSerial(年, 1, 1)) + 7
体育の日 = 第一月曜(DateSerial(年, 10, 1)) + 7
Else
成人の日 = DateSerial(年, 1, 15)
体育の日 = DateSerial(年, 10, 10)
End If

春分の日 = DateSerial(年, 3, Int(0.24242 * 年 - Int(年 / 4) + 35.84))
秋分の日 = DateSerial(年, 9, Int(0.24204 * 年 - Int(年 / 4) + 39.01))

Select Case Format(今日, "mmdd")
Case 101, 211, 429, 503, 504, 505, 720, 915, 1103, 1123, 1223, Format(春分の日, "mmdd"), Format(秋分の日, "mmdd"), Format(成人の日, "mmdd"), Format(体育の日, "mmdd")
祝日 = -1
Case Else
祝日 = 0
End Select
End Function

Public Function 第一月曜(今日 As Date) As Date
今日 = DateSerial(Year(今日), Month(今日), 1)

Do Until Weekday(今日) = 2
今日 = 今日 + 1
Loop

第一月曜 = 今日
End Function

補足

早速ご丁寧な回答を頂きまして有難うございます。可也な達人ですね！私の能力不足で全然意味が分かりません。
これはＶＢＡで行う記述なのでしょうか？精々エクセルの関数を使ったやり方しか理解できません（**）

回答No.2

imogasi

下記大事な点が明確でないと思います。補足をお願いします。
＞）(8)の日は休日 の意味は。
１０月８日（平成１４年）は火曜日だが、会社は火曜日が
毎週休みですか。またはその社員が休日を取ったのですかか。あるいはある年の１０月８日を日曜日として仮定したときの話ですか。
＞カレンダーの休日を引いた
会社の勤務日のカレンダのことですか。一般の暦の日曜日
のことですか。土曜日は休みですか。

回答No.1

maruru01

こんにちは。maruru01です。

WORKDAY関数を使うとお望みのことが出来るかも知れません。
ただし、WORKDAY関数はアドインを使って、分析ツールを組み込まないと使えません。
詳細は、ヘルプなどを参照して下さい。
OFFICEのCD-ROMが必要になるかも知れません。