マスタの締日、現在の日付から請求期間、支払予定日を計算。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
Sub GetInvoiceTerm() ' チェック If False = IsDate(UserForm1.TextBox49.Text) Then Exit Sub If False = IsNumeric(UserForm1.TextBox50.Text) Then Exit Sub If False = IsNumeric(UserForm1.TextBox51.Text) Then Exit Sub current_date = CDate(UserForm1.TextBox49.Text) ' 今回の締日 master_closing_date = CInt(UserForm1.TextBox50.Text) ' 得意先締日 payment_duration = CInt(UserForm1.TextBox51.Text) ' 支払予定(経過日数) start_day = "" end_day = "" If master_closing_date <> "30" Then ' 末締以外 If CInt(Day(current_date)) > CInt(master_closing_date) Then tmp = DateAdd("m", 1, current_date) start_day = Year(current_date) & "/" & Month(current_date) & "/" & master_closing_date + 1 end_day = Year(tmp) & "/" & Month(tmp) & "/" & master_closing_date Else tmp = DateAdd("m", -1, current_date) start_day = Year(tmp) & "/" & Month(tmp) & "/" & master_closing_date + 1 end_day = Year(current_date) & "/" & Month(current_date) & "/" & master_closing_date End If Else ' 末締の場合 start_day = Year(current_date) & "/" & Month(current_date) & "/1" end_day = Year(current_date) & "/" & Month(current_date) & "/" & GetLastDay(current_date) End If If False = IsDate(start_day) Or False = IsDate(end_day) Then ' 得意先締日の値からstart_day、end_dayが有効な日付にならなかった場合 UserForm1.TextBox41.Text = "" UserForm1.TextBox46.Text = "" UserForm1.TextBox44.Text = "" Exit Sub End If UserForm1.TextBox41.Text = Format(CDate(start_day), "yyyy/mm/dd") UserForm1.TextBox46.Text = Format(CDate(end_day), "yyyy/mm/dd") ' 支払期限算出-------------------------------------------------------- ' 支払予定(経過日数)を30で割り×月、余り×日 m = payment_duration / 30 d = payment_duration Mod 30 If True = IsLastDay(CDate(end_day)) Then ' end_dayが月末の場合は指定した月数を加算し月末にする。 tmp1 = DateAdd("m", m, CDate(end_day)) tmp1 = CDate(Year(tmp1) & "/" & Month(tmp1) & "/" & GetLastDay(tmp1)) tmp2 = DateAdd("d", d, tmp1) Else ' end_dayが月末以外 tmp1 = DateAdd("m", m, CDate(end_day)) tmp2 = DateAdd("d", d, tmp1) End If UserForm1.TextBox44.Text = Format(tmp2, "yyyy/mm/dd") End Sub Function IsLastDay(target) ' 渡された日付が月末かどうか ' 呼出側で日付型か確認済 If Day(target) = GetLastDay(target) Then IsLastDay = True Else IsLastDay = False End If End Function Function GetLastDay(target) ' 渡された日付の月の月末を取得 ' 呼出側で日付型か確認済 GetLastDay = Day(DateAdd("d", -1, Year(DateAdd("m", 1, target)) & "/" & Month(DateAdd("m", 1, target)) & "/1")) End Function |