Let’s assume that knowing the date of employment, we would be interested in the seniority of employees (in years, months, possibly days), until the end of a certain year (even the current one).
Although SQL can be very useful, creating a VBA function can help when you want to use this calculation elsewhere in the database. If the date of employment of a person is not known, there is a risk of error in that field, which could confuse us. Therefore, it is good to condition the finding of this result in advance. A function suggestion is shown below.
|
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 |
Public Function fc_Vechime(argum As Variant, int_An_bal As Integer) As String Dim d_Data_init As Date, int_Ani As Integer Dim s_an As String, s_luna As String, s_zi As String Dim nr_an As String, nr_luna As String, nr_zi As String If (VarType(argum) = vbNull) Then ' actiunea dacã este NULL fc_Vechime = "" Else d_Data_init = CDate(argum) d_Data_fin = DateValue("31.12." & int_An_bal) ' va adauga anul balantei int_Luni = DateDiff("m", d_Data_init, d_Data_fin) int_Zile = DateDiff("d", DateAdd("m", int_Luni, d_Data_init), d_Data_fin) If int_Zile < 0 Then int_Luni = int_Luni - 1 int_Zile = DateDiff("d", DateAdd("m", int_Luni, d_Data_init), d_Data_fin) End If int_Ani = int_Luni \ 12 int_Luni = int_Luni Mod 12 Select Case int_Ani Case Is <= 0 s_an = "" Case 1 s_an = int_Ani & " an" Case Else s_an = int_Ani & " ani" End Select Select Case True Case int_Luni <= 0 s_luna = "" Case int_Luni = 1 And int_Ani < 1 s_luna = int_Luni & " lunã" Case int_Luni = 1 And int_Ani >= 1 s_luna = ", " & int_Luni & " lunã" Case int_Luni > 1 And int_Ani < 1 s_luna = int_Luni & " luni" Case int_Luni > 1 And int_Ani >= 1 s_luna = ", " & int_Luni & " luni" End Select Select Case True Case int_Zile <= 0 s_zi = "" Case int_Zile = 1 And (int_Ani < 1 And int_Luni < 1) s_zi = int_Zile & " zi" Case int_Zile = 1 And (int_Ani >= 1 Or int_Luni >= 1) s_zi = ", " & int_Zile & " zi" Case int_Zile > 1 And (int_Ani < 1 And int_Luni < 1) s_zi = int_Zile & " zile" Case int_Zile > 1 And (int_Ani >= 1 Or int_Luni >= 1) s_zi = ", " & int_Zile & " zile" End Select fc_Vechime = s_an & s_luna ' & s_zi End If End Function |
A variant of calculating seniority, but using MS Excel I have shown in this post.
If it is necessary to calculate only the length of years, in order to be able to use it in the calculations, you can use this function derived from the one above.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Public Function fc_vechime_ani(argum As Variant, i_An_fin As Integer) As Integer Dim d_Data_init As Date, i_An As Integer, i_Luni As Integer, intDays As Integer, d_Data_fin As Date If (VarType(argum) = vbNull) Then ' Actiunea dacã este NULL fc_vechime_ani = 0 Else d_Data_init = CDate(argum) d_Data_fin = DateValue("31.12." & i_An_fin) i_Luni = DateDiff("m", d_Data_init, d_Data_fin) i_An = i_Luni \ 12 fc_vechime_ani = i_An End If End Function |
