• ベストアンサー

ExcelのVBAで日付指定してぃ

お家賃の振り込み日がですね 毎月25日なんですけど 25が土日だったら、その前の金曜日なんです WORKDAYとか駆使すれば出来そうな気がするのですが うまいこと思いつきません。 どなたか今月は3/23(金)になるよというマクロの数式を教えてください。

質問者が選んだベストアンサー

  • ベストアンサー
  • mt2015
  • ベストアンサー率49% (258/524)
回答No.6

ANo.2です。 > 補足です。ワークシート関数だと、狭くて長くて複雑になりそうでしかもアトから見たら絶対に解読出来ない自信が有りますのでハナから諦めてVBAでと思ってます VBから入った口でしょうか?通常、凝った配列数式でも使わない限りセル関数の方が出来ることが少ない分解りやすいと思います。 以下にサンプルコードを載せます。 ANo.1の補足にあった「祝日の場合はもうひとつ前日になります」が関係するのは11/23の勤労感謝の日と12/23の天皇誕生日、それと秋分の日だけだと思いますので、この3つのみ処理を入れています。 勤労感謝の日と天皇誕生日は固定の日付ですが、秋分の日は毎年変わります。 正確な秋分の日は前年に発表されますが、国立天文台のサイトに2030年まで予測が出ていましたので参照したところ、2022/9/23(金)だけが影響しますので秋分の日の処理は2022年9月のみ行います。 ただし、今上天皇退位後は2/23が天皇誕生日になりますし、12/23は平日になりますので、そこは今後修正が必要です。 Function dPayDay() As Date   dPayDay = DateValue(Format(Now(), "YYYY/MM/25"))   nWeek = WorksheetFunction.Weekday(dPayDay, 16)   If nWeek = 1 Then     dPayDay = dPayDay - 1        ElseIf nWeek = 2 Then     nSundayWork = 2     Select Case Month(dPayDay)     Case 9       If Year(dPayDay) = 2022 Then nSundayWork = 3     Case 11, 12       nSundayWork = 3     End Select     dPayDay = dPayDay - nSundayWork   End If End Function

Passerby01
質問者

お礼

dPayDay = DateValue(Format(Now(), "YYYY/MM/25")) なんと! そういった書式がいけるのですね!びっくりです! >VBから入った口でしょうか? VB6から入ったクチですw 小難しいことはユーザ定義関数にしてしまえっ と思っております。

すると、全ての回答が全文表示されます。

その他の回答 (7)

  • mt2015
  • ベストアンサー率49% (258/524)
回答No.8

ANo.6と同じことを作業セルを使用したワークシート関数でやってみました。 作業セルとしてD1セルに↓ =TODAY()-DAY(TODAY())+25 D2セルに↓ =WEEKDAY(D1) これで本月の支払日は↓と、なります =D1-(D2=7)-2*(D2=1)-((D2=1)*(MONTH(D1)>10))-(D1=DATE(2022,9,25)) 作業セルを使用しなくてもできますが、長くなって見づらいです。

すると、全ての回答が全文表示されます。
  • imogasi
  • ベストアンサー率27% (4737/17070)
回答No.7

>WORKDAYとか駆使すれば出来そうな気がするのすが 出来れば関数でというのが本音だろうと思い、関数でもできそうに思って。 下記ではどうですか。 毎月10日の例です。 F1:G21に祝日を定義(2018年の例です。毎年名前定義を定義しなおしする必要あり) 祝日 2018/1/1 元日 2018/1/8 成人の日 2018/2/11 建国記念の日 2018/2/12 振替休日 2018/3/21 春分の日 2018/4/29 昭和の日 2018/4/30 振替休日 2018/5/3 憲法記念日 2018/5/4 みどりの日 2018/5/5 こどもの日 2018/7/16 海の日 2018/8/11 山の日 2018/9/17 敬老の日 2018/9/23 秋分の日 2018/9/24 振替休日 2018/10/8 体育の日 2018/11/3 文化の日 2018/11/23 勤労感謝の日 2018/12/23 天皇誕生日 2018/12/24 振替休日 シートのA,B列で、B列は関数の結果です。 2018/1/10 2018/1/10 2018/2/10 2018/2/13 2018/3/10 2018/3/12 2018/4/10 2018/4/10 2018/5/10 2018/5/10 2018/6/10 2018/6/11 1900/7/10 1900/7/10 2018/8/10 2018/8/10 2018/9/10 2018/9/10 2018/10/10 2018/10/10 2018/11/10 2018/11/12 2018/12/10 2018/12/10 2018/11/3 2018/11/5 B1の式は =WORKDAY(A1-1,1,祝日) 下方向に式を複写。 結果は常時B列。 回答でもできるとも、できないとも 言っていないようなので、興味を持ったので。 祝日を含まない点で、毎日10日の例だと祝日にヒットしないようなので、適当な例でテストしなおして、チェックしてください。エクセル使いの諸賢の批判を待ちます。

すると、全ての回答が全文表示されます。
  • bunjii
  • ベストアンサー率43% (3589/8249)
回答No.5

回答No.3の追加です。 >祝日の場合はもうひとつ前日になります 回答No.3の計算結果が祝日のときはCOUNTIF関数で減算する必要がありますので祝日一覧が何処に記載されているかを提示してください。 条件が整備されていないとワークシート関数、VBAの何れでも的確な回答になりません。 減算用の数式は次のようになります。 COUNTIF([祝日一覧],DATE(YEAR(A1),MONTH(A1),25)-MAX(WEEKDAY(DATE(YEAR(A1),MONTH(A1),25),2)-5,0)) 尚、[祝日一覧]は祝日の日付を記載したセル範囲のことです。

すると、全ての回答が全文表示されます。
  • HohoPapa
  • ベストアンサー率65% (455/693)
回答No.4

こんなカンジでいかがでしょうか。 '引数の日付から過去に遡り、最初に見つかる平日の日付を返す。 Function GetPayDay(MyRange As Range) As Date  Dim wkDay As Date  wkDay = MyRange.Value    Do   If ((Weekday(wkDay) <> 1) And (Weekday(wkDay) <> 7)) Then    GetPayDay = wkDay    Exit Function   Else    wkDay = wkDay - 1   End If  Loop End Function なお、エラー処理  ・引数を省略する  ・引数に日付以外のセルを与える といったことを考慮していません。

すると、全ての回答が全文表示されます。
  • bunjii
  • ベストアンサー率43% (3589/8249)
回答No.3

>WORKDAYとか駆使すれば出来そうな気がするのですが 「WORKDAY」ではなく「WEEKDAY」を使ってください。 >どなたか今月は3/23(金)になるよというマクロの数式を教えてください。 安易にマクロ(VBA)を使わない方が良いです。 ワークシート関数で簡単に算出できるときはマクロを使わないことが賢明です。 仮にA1セルへ今日の日付として「=TODAY()」という数式が設定されているとして、振り込み日は次の数式で算出できます。 =DATE(YEAR(A1),MONTH(A1),25)-MAX(WEEKDAY(DATE(YEAR(A1),MONTH(A1),25),2)-5,0) もし、A1セルに今日の日付を算出できないときは上記数式の「A1」を「TODAY()」に置き換えてください。 >ワークシート関数だと、狭くて長くて複雑になりそうでしかもアトから見たら絶対に解読出来ない自信が有りますのでハナから諦めてVBAでと思ってます VBAの方が解読困難になると思います。(自分でコーディングできないのですから他人のコーディングを解読できないのではないでしょうか?)

すると、全ての回答が全文表示されます。
  • mt2015
  • ベストアンサー率49% (258/524)
回答No.2

「マクロの数式」と言う言葉からセル演算式とVBAマクロのどちらが必要なのか解りませんが、セル演算式ならこんな感じです。 =TODAY() - DAY(NOW()) +30-MAX(WEEKDAY(TODAY() -DAY(NOW()) +25,2),5) WEEKDAY関数の第二引数が2の場合、月~金:1~5、土曜:6、日曜:7となりますので、 当月25日の5日後の日付 - WEEKDAY(○,2)の返り値と5の大きい方 で支払日を出しています。 なお、25日や25日が土日だった場合の直前の金曜日が祝祭日だった場合は考慮して居ません。

Passerby01
質問者

補足

補足です。ワークシート関数だと、狭くて長くて複雑になりそうでしかもアトから見たら絶対に解読出来ない自信が有りますのでハナから諦めてVBAでと思ってます

すると、全ての回答が全文表示されます。
  • mdmp2
  • ベストアンサー率55% (438/787)
回答No.1

Weekday(参照値,1) が使えます。 Weekday(x,1) は曜日を数値で返します。日曜日が1、 月曜日が2 ....というように、 25日のWeekday() が1 の場合は25日から2を引き23日とし、7の場合は25日から1を引いて24日とすると良いのではありませんか? 祝休日だったらどうしますか?

Passerby01
質問者

補足

祝日の場合はもうひとつ前日になります 今年は11/25が日曜で、11/24が土曜で11/23が勤労感謝の日で11/22の木曜日になります。

すると、全ての回答が全文表示されます。

関連するQ&A