Days360 / Dayc
Days360 Function จะคิดทุกเดือนเป็น 30 วันเท้ากันหมด
Function dayc เป็นฟังก์ชั่นที่ผมสร้างเอง ซึ่งจะพิจารณาวันในแต่ละเดือน และ เดือนกุมภาพันธ์ที่มี 28 ,29 วันด้วย ตามด้าน code ล่าง ต้องก่อนใช้งานต้องติดตั้งก่อน
ปล. ผมทดสอบไว้ระดับหนึ่ง แต่เนื่องจากเวลาไม่ค่อยมีเวลาตรวจทาน หากพบผิดพลาดแจ้งได้ครับ
ผมเขียน function ที่ใช้แทน Days360 ของ excel ชื่อ dayc() เวลาใช้ก็ใส่ วันที่แรก กับวันที่สุดท้าย คล้ายๆ Days360 ตัวอย่าง =dayc("1/1/2000","31/12/2001")
Option Explicit
Function dayc(Day1 As String, Day2 As String) As Double Dim X, Y As Integer '--------------------------------------------------------------------------------------------------- Dim endd As Integer Dim m(12) As Integer Dim arrayDay(366) As String Dim arrayDay2(366) As String Dim xDate As String Dim i, j As Integer 'Dim s, t As Integer Dim A As Integer
Dim n_First As Integer Dim n_Last As Integer Dim n_Mid As Integer '--------------------------------------------------------------------------------------------------- prepare value for arrayDay(1-366) A = 0 xDate = Day1 If Year(xDate) Mod 4 = 0 Then endd = 366 m(2) = 29 Else endd = 365 m(2) = 28 End If m(1) = 31: m(3) = 31: m(4) = 30: m(5) = 31: m(6) = 30: m(7) = 31: m(8) = 31: m(9) = 30: m(10) = 31: m(11) = 30: m(12) = 31 For i = 1 To 12 For j = 1 To m(i) A = A + 1 arrayDay(A) = j & "-" & i Next j Next i '---------------------------------------------------------------------------------------------------prepare value for arrayDay2(1-366) A = 0 xDate = Day2 If Year(xDate) Mod 4 = 0 Then endd = 366 m(2) = 29 Else endd = 365 m(2) = 28 End If m(1) = 31: m(3) = 31: m(4) = 30: m(5) = 31: m(6) = 30: m(7) = 31: m(8) = 31: m(9) = 30: m(10) = 31: m(11) = 30: m(12) = 31 For i = 1 To 12 For j = 1 To m(i) A = A + 1 arrayDay2(A) = j & "-" & i Next j Next i
'---------------------------------------------------------------------------------------------------
If Year(Day1) = Year(Day2) Then '-- same year If Month(Day1) = Month(Day2) Then '-- same month dayc = Day(Day2) - Day(Day1) ElseIf Month(Day1) < Month(Day2) Then '-- not same month For i = 1 To endd If arrayDay(i) = Day(Day1) & "-" & Month(Day1) Then X = i: Exit For End If Next i For i = 1 To endd If arrayDay2(i) = Day(Day2) & "-" & Month(Day2) Then Y = i: Exit For End If Next i dayc = Y - X Else dayc = -0 End If
ElseIf Year(Day2) - Year(Day1) > 1 Then '-- corss over to more then 1 year '----------verify first Year (between Day1 - 31/Dec/year(Day1)) n_First = 0 If Year(Day1) Mod 4 = 0 Then endd = 366 Else endd = 365 End If For i = 1 To endd If arrayDay(i) = Day(Day1) & "-" & Month(Day1) Then Y = i: Exit For End If Next i n_First = endd - Y '----------verify last Year (between 1/Jan/year(Day2) - Day2) n_Last = 0 Y = 0 If Year(Day2) Mod 4 = 0 Then endd = 366 Else endd = 365 End If For i = 1 To endd If arrayDay2(i) = Day(Day2) & "-" & Month(Day2) Then Y = i: Exit For End If Next i n_Last = Y '----------verify the full Year (between first year - last year) n_Mid = 0 For i = 1 To (Year(Day2) - Year(Day1)) - 1 If (Year(Day1) + i) Mod 4 = 0 Then n_Mid = n_Mid + 366 Else n_Mid = n_Mid + 365 End If Next i '----------sum together 'dayc = n_First 'dayc = n_Last 'dayc = n_Mid dayc = n_First + n_Last + n_Mid ElseIf Year(Day2) - Year(Day1) = 1 Then '-- corss over to next year '----------verify first Year (between Day1 - 31/Dec/year(Day1)) n_First = 0 If Year(Day1) Mod 4 = 0 Then endd = 366 Else endd = 365 End If For i = 1 To endd If arrayDay(i) = Day(Day1) & "-" & Month(Day1) Then Y = i: Exit For End If Next i n_First = endd - Y '----------verify last Year (between 1/Jan/year(Day2) - Day2) n_Last = 0 Y = 0 If Year(Day2) Mod 4 = 0 Then endd = 366 Else endd = 365 End If For i = 1 To endd If arrayDay2(i) = Day(Day2) & "-" & Month(Day2) Then Y = i: Exit For End If Next i n_Last = Y '----------sum together 'dayc = n_First 'dayc = n_Last dayc = n_First + n_Last Else dayc = -0 End If
End Function
Create Date : 27 พฤษภาคม 2558 |
Last Update : 27 พฤษภาคม 2558 9:01:08 น. |
|
0 comments
|
Counter : 586 Pageviews. |
|
|