littlepete
Well-known Member
- Joined
- Mar 26, 2015
- Messages
- 507
- Office Version
- 365
- Platform
- Windows
hello
The part in my formula talking about age has three parts:
1. " Pete is 60 years, 11 months and 27 days " (really!) ... = the case where the person is not dead: q6<>"" (born is not empty) and am6="" (deceased is empty) => age from q6 till today. (the udf avoids to show zero values too)...
2. " Pete has died. He was 84 years and 17 days " = the person has died: present the age when he died: from q6 till am6...
3. " he would have been 93 years and 17 days today " = the age that the deceased person would have today... age from q6 till today
column Q =date of birth; column AM = date of death.
i use an UDF for part 1 which works very well:
i'm not finding the adjustment to construct part 2 and 3...
could someone copy this UDF and make the two other parts: besides leeftijd it could be named part 2 leeftijdovl and part 3 leeftijdvd ...
hoping for a solution
wishing you all a great Eastern !!!
thank you !
The part in my formula talking about age has three parts:
1. " Pete is 60 years, 11 months and 27 days " (really!) ... = the case where the person is not dead: q6<>"" (born is not empty) and am6="" (deceased is empty) => age from q6 till today. (the udf avoids to show zero values too)...
2. " Pete has died. He was 84 years and 17 days " = the person has died: present the age when he died: from q6 till am6...
3. " he would have been 93 years and 17 days today " = the age that the deceased person would have today... age from q6 till today
column Q =date of birth; column AM = date of death.
i use an UDF for part 1 which works very well:
VBA Code:
Function Leeftijd(geboorte) ' geboorte=day of birth
maanden = DateDiff("m", geboorte, Date) + (Day(geboorte) > Day(Date))
d = CInt(Date - WorksheetFunction.EDate(geboorte, maanden))
If maanden Mod 12 > 1 And d > 1 Then
Leeftijd = Int(maanden / 12) & " jaar, " & maanden Mod 12 & " maanden en " & d & " dagen "
ElseIf maanden Mod 12 > 1 And d = 1 Then
Leeftijd = Int(maanden / 12) & " jaar, " & maanden Mod 12 & " maanden en " & d & " dag "
ElseIf maanden Mod 12 > 1 And d = 0 Then
Leeftijd = Int(maanden / 12) & " jaar en " & maanden Mod 12 & " maanden "
ElseIf maanden Mod 12 = 1 And d > 1 Then
Leeftijd = Int(maanden / 12) & " jaar, " & maanden Mod 12 & " maand en " & d & " dagen "
ElseIf maanden Mod 12 = 1 And d = 1 Then
Leeftijd = Int(maanden / 12) & " jaar, " & maanden Mod 12 & " maand en " & d & " dag "
ElseIf maanden Mod 12 = 1 And d = 0 Then
Leeftijd = Int(maanden / 12) & " jaar en " & maanden Mod 12 & " maand "
ElseIf maanden Mod 12 = 0 And d > 1 Then
Leeftijd = Int(maanden / 12) & " jaar en " & d & " dagen "
ElseIf maanden Mod 12 = 0 And d = 1 Then
Leeftijd = Int(maanden / 12) & " jaar en " & d & " dag "
ElseIf maanden Mod 12 = 0 And d = 0 Then
Leeftijd = Int(maanden / 12) & " jaar "
End If
Delta = CInt(WorksheetFunction.EDate(geboorte, WorksheetFunction.MRound(maanden - (Day(geboorte) > Day(Date)), 12)) - Date) 'even afronden op een jaar
If Abs(Delta) < 20 Then
Select Case Delta
Case 0: s = "VERJAART VANDAAG !"
Case -1: s = "verjaarde gisteren"
Case -2: s = "verjaarde eergisteren"
Case 1: s = "verjaart morgen"
Case 2: s = "verjaart overmorgen"
Case 3: s = "verjaart binnen 3 dagen"
Case -20 To -3: s = "verjaarde " & -Delta & " dagen geleden"
Case 3 To 20: s = "verjaart binnen " & Delta & " dagen"
End Select
Leeftijd = Leeftijd & "oud (" & s & ")."
End If
End Function
i'm not finding the adjustment to construct part 2 and 3...
could someone copy this UDF and make the two other parts: besides leeftijd it could be named part 2 leeftijdovl and part 3 leeftijdvd ...
hoping for a solution
wishing you all a great Eastern !!!
thank you !