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 !!!

 

[BSALV]

with a little help of a friend : Translator

=IF(BY6="","",IF(AND(DATEDIF(BY6,NOW(),"y")<>0,
DATEDIF(BY6,NOW(),"ym")<>0),
DATEDIF(BY6,NOW(),"y")&" jaar en "&DATEDIF(BY6,NOW(),"ym")&" maand ","")
&IF(AND(DATEDIF(BY6,NOW(),"y")<>0,
DATEDIF(BY6,NOW(),"ym")=0),
DATEDIF(BY6,NOW(),"y")&" jaar ","")

 

[littlepete]

hello all

i'm still trying to replace the many characters-datediff (dutch: datumverschil) into a udf called dv

i learned that the string deciding on days months or years should come first.
but still it gives an error.
because i use a "dutch excel" you should think you need to change all , into ; but i learned in a udf (in my case that is placed into a formula)
you have to form the vba in the english way...

so why is it still not working?

this is what it should do (just one example)

each row is a person; day of birth in column Q ; in column F it says "person x was born on tuesday, 15 march 1960 at 14:55"
in column E it says "person x is 60 years and 3 months old. his birthday is tomorrow".
for that second line, the part of the formula to say that is :

[B]&ALS(AM6="";
ALS(365-DATUMVERSCHIL(Q6;VANDAAG();"yd")=2;"verjaart overmorgen"&TEKEN(10);"")
&ALS(365-DATUMVERSCHIL(Q6;VANDAAG();"yd")=1;"verjaart morgen"&TEKEN(10);"")
&ALS(EN(MAAND(Q6)=MAAND(VANDAAG());DAG(Q6)=DAG(VANDAAG()));"◀ verjaart vandaag ! ▶"&TEKEN(10);"");"")[/B]

as you can see the function "datumverschil" (english: datediff) is in these three lines already two times.
it calculates the years between birth (column Q) and today (dutch: vandaag)...
the part " datumverschil(q6;vandaag();"yd") should be in a udf saying dv(q6;vandaag();"yd")

so i have:

[B]Function dv(eerste As Range, tweede As Range, str As String)
dv = DateDiff(str, eerste, tweede)[/B]
End Function

str is a string containing "yd"; eerste (english: first) is day of birth ; tweede (english: second) is date of today.

what should i change to get my "dv" udf in my formula ?

have a nice sunday !

 

[petertenthije]

Do you remember this thread you posted a while back?

what do i have to do to get answers on my mostly probably easy questions ????

i know i should remain polite, but why am i getting NOOOOOOOO answers on questions that i think are even not that difficult !!! i am reading soooooo many answers THAT ALLLLL are wrong !!! BECAUSE I DO try them all !!! hoping to learn good stuff ! thx... if you decide to help a bit pete.

www.mrexcel.com

Now: read this reply you gave to someone trying to help you, and aks yourself that first question again…

let me recommend to you google translate it will tell you : nu = now.
i also told you i'm using my udf in formula, you know what that is?
i also told you that the reason i'm making udf is because my formula is at its limits (8192 characters)
so you also know what i presented as code is only a very small part of it.
and because i 'm sure you know how a datediff function works,

i'm asking myself why you are asking ten million questions, while you perfectly well
just could tell me how to write an udf to replace the datediff function.

thank you so far for only taking my time and making me angry.
i will not ask you anything else. just read again all the decent answers i already gave.

 

[littlepete]

Do you remember this thread you posted a while back?

what do i have to do to get answers on my mostly probably easy questions ????

i know i should remain polite, but why am i getting NOOOOOOOO answers on questions that i think are even not that difficult !!! i am reading soooooo many answers THAT ALLLLL are wrong !!! BECAUSE I DO try them all !!! hoping to learn good stuff ! thx... if you decide to help a bit pete.

www.mrexcel.com

Now: read this reply you gave to someone trying to help you, and aks yourself that first question again…

no need to hunt me down with that post. you prove i 'm right. i'm a very very patient person... and thank you for helping?

 

[BSALV]

you're not very polite

Map1
	A	B	C	D	E	F	G
1		number of months to nearest birthday	closest birthdate	delta	comment		number of months
2	21/07/1942	960	21/07/2022	123			955
3	9/04/1949	876	9/04/2022	20	verjaart binnen 20 dagen		875
4	6/07/1993	348	6/07/2022	108			344
5	20/01/2012	120	20/01/2022	-59			122
6	23/10/2000	252	23/10/2021	-148			256
7	10/06/1926	1152	10/06/2022	82			1149
8	22/04/1998	288	22/04/2022	33			286
9	16/06/1991	372	16/06/2022	88			369
10	7/03/1950	864	7/03/2022	-13	Verjaarde 13 dagen terug		864
11	24/09/1931	1092	24/09/2022	188			1085
12	26/12/1929	1104	26/12/2021	-84			1106
13	5/10/1976	540	5/10/2021	-166			545
14	16/07/1920	1224	16/07/2022	118			1220
15	25/06/1987	420	25/06/2022	97			416
16	14/05/1996	312	14/05/2022	55			310
17	20/03/1937	1020	20/03/2022	0	happy birthday		1020
18	13/07/1964	696	13/07/2022	115			692
19	20/05/1943	948	20/05/2022	61			946
20	17/01/1954	816	17/01/2022	-62			818
21	21/02/1978	528	21/02/2022	-27	Verjaarde 27 dagen terug		528
22	29/09/1939	996	29/09/2022	193			989
23	28/11/1929	1104	28/11/2021	-112			1107
24	27/10/1932	1068	27/10/2021	-144			1072
25	26/12/1977	528	26/12/2021	-84			530
26	7/05/2008	168	7/05/2022	48			166
27	27/11/1983	456	27/11/2021	-113			459
28	14/11/1954	804	14/11/2021	-126			808
29	10/11/1964	684	10/11/2021	-130			688
30	22/05/1919	1236	22/05/2022	63			1233
31	18/01/1938	1008	18/01/2022	-61			1010
32	13/04/1954	816	13/04/2022	24	verjaart binnen 24 dagen		815
33	7/11/1940	972	7/11/2021	-133			976
34	29/04/1930	1104	29/04/2022	40			1102
35	29/10/1924	1164	29/10/2021	-142			1168
36	18/01/1999	276	18/01/2022	-61			278
37	18/11/2017	48	18/11/2021	-122			52
38	8/05/1911	1332	8/05/2022	49			1330
39	29/01/2012	120	29/01/2022	-50			121
Blad1

Cell Formulas
Range	Formula
A2:A39	A2	=RANDBETWEEN(DATE(1910,1,1),TODAY())
B2:B39	B2	=MROUND(YEAR(TODAY())*12+MONTH(TODAY())-12*YEAR(A2)-MONTH(A2),12)
C2:C39	C2	=+EDATE(A2,B2)
D2:D39	D2	=-TODAY()+C2
E2:E39	E2	=IF(ABS(D2)<30,CHOOSE(SIGN(D2)+2,"Verjaarde "&-D2 & " dagen terug","happy birthday","verjaart binnen "&D2&" dagen"),"")
G2:G39	G2	=DATEDIF(A2,TODAY(),"m")

 

[littlepete]

ok, for all those who think the same way as you, i really would like to apologise sincerely. in every possible language, so "sorry excuse-moi, entschuldige mich, perdone, ..."
but: watching your reply i must ask: where do i see a UDF doing what i 'm looking for ?

your line G2 " datedif(a2, today(), "m") would be in dutch: " datumverschil("m"; a2; vandaag())

so i still wonder how this udf with three variables (date, date, string) has to be composed?

sofar and still, i have this:

Public Function dv(eerste As Date, tweede As Date, str As String) As Variant
' ==================================================== datumverschil
dv = DateDiff(str, eerste, tweede)
End Function

thank you for any help and have a great sunday !

 

[BSALV]

ssss.xlsm
	A	B	C	D	E	F
1		number of months to nearest birthday	closest birthdate	delta	comment	UDF
2	10/05/1950	864	10/05/2022	51		71 years, 10 months, 10 days
3	3/08/1968	648	3/08/2022	136		53 years, 7 months, 17 days
4	30/11/1918	1236	30/11/2021	-110		103 years, 3 months, 20 days
5	7/12/1978	516	7/12/2021	-103		43 years, 3 months, 13 days
6	19/01/2012	120	19/01/2022	-60		10 years, 2 months, 1 days
7	21/07/1971	612	21/07/2022	123		50 years, 7 months, 27 days
8	14/08/1965	684	14/08/2022	147		56 years, 7 months, 6 days
9	15/08/1929	1116	15/08/2022	148		92 years, 7 months, 5 days
10	9/04/1945	924	9/04/2022	20	verjaart binnen 20 dagen	76 years, 11 months, 11 days
11	30/07/1968	648	30/07/2022	132		53 years, 7 months, 20 days
12	20/06/1943	948	20/06/2022	92		78 years, 9 months, 0 days
13	14/12/1956	780	14/12/2021	-96		65 years, 3 months, 6 days
14	14/08/1986	432	14/08/2022	147		35 years, 7 months, 6 days
15	15/10/1948	876	15/10/2021	-156		73 years, 5 months, 5 days
16	5/02/2008	168	5/02/2022	-43		14 years, 1 months, 15 days
17	10/01/2006	192	10/01/2022	-69		16 years, 2 months, 10 days
18	14/05/1951	852	14/05/2022	55		70 years, 10 months, 6 days
19	9/11/1949	864	9/11/2021	-131		72 years, 4 months, 11 days
20	8/11/1971	600	8/11/2021	-132		50 years, 4 months, 12 days
21	17/03/1964	696	17/03/2022	-3	Verjaarde 3 dagen terug	58 years, 0 months, 3 days|Verjaardag 3 dagen terug
22	19/05/2014	96	19/05/2022	60		7 years, 10 months, 1 days
23	28/03/2011	132	28/03/2022	8	verjaart binnen 8 dagen	10 years, 11 months, 20 days|Verjaardag binnen 8 dagen
24	6/11/1981	480	6/11/2021	-134		40 years, 4 months, 14 days
25	4/06/1922	1200	4/06/2022	76		99 years, 9 months, 16 days
26	16/03/1931	1092	16/03/2022	-4	Verjaarde 4 dagen terug	91 years, 0 months, 4 days|Verjaardag 4 dagen terug
27	15/02/2011	132	15/02/2022	-33		11 years, 1 months, 5 days
28	2/06/2012	120	2/06/2022	74		9 years, 9 months, 18 days
29	7/09/1954	816	7/09/2022	171		67 years, 6 months, 13 days
30	17/07/1911	1332	17/07/2022	119		110 years, 8 months, 3 days
31	9/07/1970	624	9/07/2022	111		51 years, 8 months, 11 days
32	6/01/1987	420	6/01/2022	-73		35 years, 2 months, 14 days
33	13/07/1918	1248	13/07/2022	115		103 years, 8 months, 7 days
34	12/05/1958	768	12/05/2022	53		63 years, 10 months, 8 days
35	1/06/1920	1224	1/06/2022	73		101 years, 9 months, 19 days
36	18/08/1914	1296	18/08/2022	151		107 years, 7 months, 2 days
37	30/09/1939	996	30/09/2022	194		82 years, 5 months, 20 days
38	2/09/1958	768	2/09/2022	166		63 years, 6 months, 18 days
39	16/01/1936	1032	16/01/2022	-63		86 years, 2 months, 4 days
Blad1

Cell Formulas
Range	Formula
A2:A39	A2	=RANDBETWEEN(DATE(1910,1,1),TODAY())
B2:B39	B2	=MROUND(YEAR(TODAY())*12+MONTH(TODAY())-12*YEAR(A2)-MONTH(A2),12)
C2:C39	C2	=+EDATE(A2,B2)
D2:D39	D2	=-TODAY()+C2
E2:E39	E2	=IF(ABS(D2)<30,CHOOSE(SIGN(D2)+2,"Verjaarde "&-D2 & " dagen terug","happy birthday","verjaart binnen "&D2&" dagen"),"")
F2:F39	F2	=leeftijd(A2)

 

[littlepete]

hello...

can you please check my udf to see what's wrong with it? thanks ...

 

[rlv01]

' ===================== datumverschil (date difference)
Public Function dv(eerste As Range, tweede As Range, Interval As String) As Variant

    Dim DiffDate As Long

    Interval = LCase(Trim(Interval))
    Select Case Interval
    Case "yyyy"                                       '= Year
        DiffDate = DateDiff("yyyy", eerste, tweede)
    Case "q"                                          '= Quarter
        DiffDate = DateDiff("q", eerste, tweede)
    Case "m"                                          '= Month
        DiffDate = DateDiff("m", eerste, tweede)
    Case "y"                                          '= Day of year
        DiffDate = DateDiff("y", eerste, tweede)
    Case "d"                                          '= Day
        DiffDate = DateDiff("d", eerste, tweede)
    Case "w"                                          '= Weekday
        DiffDate = DateDiff("w", eerste, tweede)
    Case "ww"                                         ' = Week
        DiffDate = DateDiff("ww", eerste, tweede)
    Case "h"                                          '= Hour
        DiffDate = DateDiff("h", eerste, tweede)
    Case "n"                                          '= Minute
        DiffDate = DateDiff("n", eerste, tweede)
    Case "s"                                          '= Second
        DiffDate = DateDiff("s", eerste, tweede)
    Case Else
        DiffDate = "Fout!"
    End Select

    dv = DiffDate
End Function

 

[littlepete]

hello !

thank you so much for this udf... i do understand every part of it, but sadly it does not yet work... it still gives a error on value...

i put " =dv("m";q6;am6) " in a cell q6 day of birth; am6 day of death...
i have placed msgbox between all lines but not one msgbox shows up...

my suspicion is it has to do with me being in belgium, flemish, and using therefor the dutch excel version,
although i learned in vba you still use the english terms... i did try replacing , with ; but no result...

to be clear: the cells in eerste and tweede (first and second) are all valid dates, with the cells having a date number format being "dddd.mm.jjjj" ...

the search continues !!! (thank you so far !!!)