udf to calculate date difference value error

[littlepete]

hello

an udf to compose all parts of a name (title, surname, first names, last name, transcription if other script) that is working
made me very happy !
i'm trying now to use (datumverschil in dutch: ) datedifference. it has three parts: first date; second date; way of measuring...

this is why i'm trying... probably i'm forgetting something?
i try to use it this way : " dv(q6;am6;"d") "

Function dv(eerste As Range, tweede As Range, str As String)
dv = DateDiff(eerste, tweede, str)
End Function

thank you for the correction or help !!!

 

[littlepete]

     delta =...
     Select Case Delta
          Case 0: s = "Happy birthday"
          Case -1: s = "yesterday ..."
          Case -2: s = "before yesterday ..."
          Case 1: s = "tomorrow ..."
          Case 2 = "the day after tomorrow"
          Case -20 To -3: s = "Verjaardag " & -Delta & " dagen terug"
          Case 3 To 20: s = "Verjaardag binnen " & Delta & " dagen"
          Case Else: s = ""
     End Select
     if len(s)>0 then Leeftijd = Leeftijd & "|" & s

to give you an idea, a printscreen of my fake data showing you all i have you will see in column F that your udf is in it !!!
i am now trying to avoid with that leeftijd udf to have " he is 35 years, 3 months and 0 days " (avoiding zeros)

thank you !!!

 

[ebea]

to give you an idea, a printscreen of my fake data showing you all i have you will see in column F that your udf is in it !!!
i am now trying to avoid with that leeftijd udf to have " he is 35 years, 3 months and 0 days " (avoiding zeros)

thank you !!!

Not a solution to your question, but an alternative to your "Project". www.ancestris.org

 

[littlepete]

Not a solution to your question, but an alternative to your "Project". www.ancestris.org

i already have my family tree, made in myheritage.nl going back to around 1700 thanks !!!

 

[BSALV]

it's like a spill formula, so you need 3 cells horizontal or with transpose 3 cells vertical (green cells)
You can also ask 1 item with the index-formula (orange cells).
if day of death is not a date or less than birthday then he/she is still alive
The day of death is optional, so if he/she is still alive, you're not obliged to add. (3rd example)

Sub test()
     x = Leeftijd(DateSerial(1933, 1, 12), DateSerial(1998, 4, 2))
End Sub



Function Leeftijd(geboorte, Optional sterfte)                   'geboorte=day of birth
     Dim result(2), Dead                                        'array met 3 elementen (0-2)

     Dead = 0
     On Error Resume Next
     If IsDate(sterfte) Then If sterfte >= geboorte Then Dead = sterfte
     On Error GoTo 0

     '1e berekening = datumverschil tot vandaag
     maanden = DateDiff("m", geboorte, Date) + (Day(geboorte) > Day(Date))
     d = CInt(Date - WorksheetFunction.EDate(geboorte, maanden))
     result(0) = Int(maanden / 12) & " years, " & maanden Mod 12 & " months, " & d & " days"

     If Dead > Leeftijd Then                                    'is overleden (want datum na geboorte)
          result(2) = "He/she would be " & result(0): result(0) = "Deceased"     'is die overleden, dan zet je dat datumverschil in result(2) en "deceased" in result(0)
     Else
          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 Sgn(Delta)
                    Case -1: s = "Verjaardag " & -Delta & " dagen terug"
                    Case 0: s = "Happy birthday"
                    Case 1: s = "Verjaardag binnen " & Delta & " dagen"
               End Select
          End If

          result(2) = "Still alive": result(0) = result(0) & IIf(Len(s) > 0, " ", "") & s
     End If

     If Dead >= geboorte Then
          maanden = DateDiff("m", geboorte, Dead) + (Day(geboorte) > Day(Dead))
          d = CInt(Dead - WorksheetFunction.EDate(geboorte, maanden))
          result(1) = "Deceased at age of  " & Int(maanden / 12) & " years, " & maanden Mod 12 & " months, " & d & " days"     'echte leeftijd
     Else
          result(1) = "Alive and kicking"
     End If

     Leeftijd = result
End Function

l_pete.xlsm
	A	B	C	D	E	F
1
2	12/01/1933	4/04/1998		Deceased	Deceased at age of 65 years, 2 months, 23 days	He/she would be 89 years, 3 months, 3 days
3
4
5				Deceased		Deceased
6						Deceased at age of 65 years, 2 months, 23 days
7				Deceased at age of 65 years, 2 months, 23 days		He/she would be 89 years, 3 months, 3 days
8
9				He/she would be 89 years, 3 months, 3 days
10
11
12
13
14
15
16	1/01/2000	1/01/1999		22 years, 3 months, 14 days	Alive and kicking	Still alive
17
18
19				22 years, 3 months, 14 days		22 years, 3 months, 14 days
20						Alive and kicking
21				Alive and kicking		Still alive
22
23				Still alive
24
25
26
27
28
29
30
31
32	15/04/1922			100 years, 0 months, 0 days Happy birthday	Alive and kicking	Still alive
33
34
35				100 years, 0 months, 0 days Happy birthday		100 years, 0 months, 0 days Happy birthday
36						Alive and kicking
37				Alive and kicking		Still alive
38
39				Still alive
40
Blad1

Cell Formulas
Range	Formula
D2:F2,D16:F16	D2	=leeftijd(A2,B2)
D5	D5	=INDEX(leeftijd($A$2,$B$2),1)
F5:F7	F5	=TRANSPOSE(leeftijd($A$2,$B$2))
D7	D7	=INDEX(leeftijd($A$2,$B$2),2)
D9	D9	=INDEX(leeftijd($A$2,$B$2),3)
D19	D19	=INDEX(leeftijd($A$16,$B$16),1)
F19:F21	F19	=TRANSPOSE(leeftijd($A$16,$B$16))
D21	D21	=INDEX(leeftijd($A$16,$B$16),2)
D23	D23	=INDEX(leeftijd($A$16,$B$16),3)
A32	A32	=EDATE(TODAY(),-1200)
D32:F32	D32	=leeftijd(A32)
D35	D35	=INDEX(leeftijd($A$32),1)
F35:F37	F35	=TRANSPOSE(leeftijd($A$32))
D37	D37	=INDEX(leeftijd($A$16),2)
D39	D39	=INDEX(leeftijd($A$16),3)
Dynamic array formulas.

 

[littlepete]

it's like a spill formula, so you need 3 cells horizontal or with transpose 3 cells vertical (green cells)
You can also ask 1 item with the index-formula (orange cells).
if day of death is not a date or less than birthday then he/she is still alive
The day of death is optional, so if he/she is still alive, you're not obliged to add. (3rd example)

Sub test()
     x = Leeftijd(DateSerial(1933, 1, 12), DateSerial(1998, 4, 2))
End Sub



Function Leeftijd(geboorte, Optional sterfte)                   'geboorte=day of birth
     Dim result(2), Dead                                        'array met 3 elementen (0-2)

     Dead = 0
     On Error Resume Next
     If IsDate(sterfte) Then If sterfte >= geboorte Then Dead = sterfte
     On Error GoTo 0

     '1e berekening = datumverschil tot vandaag
     maanden = DateDiff("m", geboorte, Date) + (Day(geboorte) > Day(Date))
     d = CInt(Date - WorksheetFunction.EDate(geboorte, maanden))
     result(0) = Int(maanden / 12) & " years, " & maanden Mod 12 & " months, " & d & " days"

     If Dead > Leeftijd Then                                    'is overleden (want datum na geboorte)
          result(2) = "He/she would be " & result(0): result(0) = "Deceased"     'is die overleden, dan zet je dat datumverschil in result(2) en "deceased" in result(0)
     Else
          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 Sgn(Delta)
                    Case -1: s = "Verjaardag " & -Delta & " dagen terug"
                    Case 0: s = "Happy birthday"
                    Case 1: s = "Verjaardag binnen " & Delta & " dagen"
               End Select
          End If

          result(2) = "Still alive": result(0) = result(0) & IIf(Len(s) > 0, " ", "") & s
     End If

     If Dead >= geboorte Then
          maanden = DateDiff("m", geboorte, Dead) + (Day(geboorte) > Day(Dead))
          d = CInt(Dead - WorksheetFunction.EDate(geboorte, maanden))
          result(1) = "Deceased at age of  " & Int(maanden / 12) & " years, " & maanden Mod 12 & " months, " & d & " days"     'echte leeftijd
     Else
          result(1) = "Alive and kicking"
     End If

     Leeftijd = result
End Function

l_pete.xlsm
	A	B	C	D	E	F
1
2	12/01/1933	4/04/1998		Deceased	Deceased at age of 65 years, 2 months, 23 days	He/she would be 89 years, 3 months, 3 days
3
4
5				Deceased		Deceased
6						Deceased at age of 65 years, 2 months, 23 days
7				Deceased at age of 65 years, 2 months, 23 days		He/she would be 89 years, 3 months, 3 days
8
9				He/she would be 89 years, 3 months, 3 days
10
11
12
13
14
15
16	1/01/2000	1/01/1999		22 years, 3 months, 14 days	Alive and kicking	Still alive
17
18
19				22 years, 3 months, 14 days		22 years, 3 months, 14 days
20						Alive and kicking
21				Alive and kicking		Still alive
22
23				Still alive
24
25
26
27
28
29
30
31
32	15/04/1922			100 years, 0 months, 0 days Happy birthday	Alive and kicking	Still alive
33
34
35				100 years, 0 months, 0 days Happy birthday		100 years, 0 months, 0 days Happy birthday
36						Alive and kicking
37				Alive and kicking		Still alive
38
39				Still alive
40
Blad1

Cell Formulas
Range	Formula
D2:F2,D16:F16	D2	=leeftijd(A2,B2)
D5	D5	=INDEX(leeftijd($A$2,$B$2),1)
F5:F7	F5	=TRANSPOSE(leeftijd($A$2,$B$2))
D7	D7	=INDEX(leeftijd($A$2,$B$2),2)
D9	D9	=INDEX(leeftijd($A$2,$B$2),3)
D19	D19	=INDEX(leeftijd($A$16,$B$16),1)
F19:F21	F19	=TRANSPOSE(leeftijd($A$16,$B$16))
D21	D21	=INDEX(leeftijd($A$16,$B$16),2)
D23	D23	=INDEX(leeftijd($A$16,$B$16),3)
A32	A32	=EDATE(TODAY(),-1200)
D32:F32	D32	=leeftijd(A32)
D35	D35	=INDEX(leeftijd($A$32),1)
F35:F37	F35	=TRANSPOSE(leeftijd($A$32))
D37	D37	=INDEX(leeftijd($A$16),2)
D39	D39	=INDEX(leeftijd($A$16),3)
Dynamic array formulas.

hello

thank you for your reply !!!
i'm not sure how to use this udf: i'm trying " leeftijd(q6) " (q is the column with birth date)
or " leeftijd(q6;am6) " (q is birth and am is date of death)

i always get an "overloop" error meaning there is a result but the udf cannot present it. i think
in english it's called "spill error"...

what should i change?
enjoy excel, and have a great Eastern !

 

[BSALV]

that UDF calculates 3 things, if you just want one of them use the index

=index(leeftijd(q6;am6);2) 
=index(leeftijd(q6);1)