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]

' ===================== 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

hello i'm not sure if i posted my answer on the correct place so here a reminder in case it was not thx !!!

 

[rlv01]

i put " =dv("m";q6;am6) " in a cell q6 day of birth; am6 day of death...

Why would you ever do that? Didn't you look at the code that I posted?

Public Function dv(eerste As Range, tweede As Range, Interval As String) As Variant

You must enter the parameters in the the order required by the function as I wrote it if you expect it to work for you.

tmp1.xlsm
	Q	R	S																				AM
1	eerste	tweede	Interval																				Result
6	1-Mar	10-Jul	d																				131
7	1-Mar	10-Jul	m																				4
Sheet5

Cell Formulas
Range	Formula
AM6:AM7	AM6	=dv(Q6,R6,S6)

 

[littlepete]

Why would you ever do that? Didn't you look at the code that I posted?

Public Function dv(eerste As Range, tweede As Range, Interval As String) As Variant

You must enter the parameters in the the order required by the function as I wrote it if you expect it to work for you.

tmp1.xlsm
	Q	R	S																				AM
1	eerste	tweede	Interval																				Result
6	1-Mar	10-Jul	d																				131
7	1-Mar	10-Jul	m																				4
Sheet5

Cell Formulas
Range	Formula
AM6:AM7	AM6	=dv(Q6,R6,S6)

hello

i was only referring to post number 20 here; i DID copy your code entirely, and then wrote post number 20 here but i forgot to answer you and just made a new post,
therefor this reminder i had answered already... so: up to number 20

 

[rlv01]

hello

i was only referring to post number 20 here; i DID copy your code entirely, and then wrote post number 20 here but i forgot to answer you and just made a new post,
therefor this reminder i had answered already... so: up to number 20

I don't understand the above. The question I have for you is why did you attempt to use =dv("m";q6;am6) when the code I posted (and the code you posted in post #1) expect to see =dv(q6;am6;"m")?

[Note that the issue of separation characters (i.e. ";" vs. "," ) is yours to resolve. My version of excel is set up to use commas (",") so for me on my PC the formula is =dv(q6,am6,"m")]

 

[littlepete]

I don't understand the above. The question I have for you is why did you attempt to use =dv("m";q6;am6) when the code I posted (and the code you posted in post #1) expect to see =dv(q6;am6;"m")?

[Note that the issue of separation characters (i.e. ";" vs. "," ) is yours to resolve. My version of excel is set up to use commas (",") so for me on my PC the formula is =dv(q6,am6,"m")]

hey...

in the beginning i did not know the difference dutch (end with string) and english (start with string) ... in my dutch formula all works perfect...
trying to put it in an udf is difficult, i am trying all differences... thxxx for the help !

 

[BSALV]

sunday, so i had to take a family break ...
My version is also dutch/flemish.
i can add "application.volatile", but that's your choice.

Function Leeftijd(geboorte)                                     'geboorte=day of birth
     maanden = DateDiff("m", geboorte, Date) + (Day(geboorte) > Day(Date))
     d = CInt(Date - WorksheetFunction.EDate(geboorte, maanden))
     Leeftijd = Int(maanden / 12) & " years, " & maanden Mod 12 & " months, " & d & " days"     'echte leeftijd

     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
          Leeftijd = Leeftijd & "|" & s
     End If
End Function

 

[littlepete]

' ===================== 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

it took some time but this is the solution, let me explain:

the INSIDE of the udf has to be completely english, so using kommas and not ";" also: the interval comes first.
the OUTSIDE is the opposit: i use a dutch excel, so formula are in dutch vba ! so:
using ";" and not kommas ; and the interval goes last !!!

that combination makes the udf with datediff / datumverschil working !!!

thank you for the help ! I am very glad to have found the solution !!!

 

[littlepete]

sunday, so i had to take a family break ...
My version is also dutch/flemish.
i can add "application.volatile", but that's your choice.

Function Leeftijd(geboorte)                                     'geboorte=day of birth
     maanden = DateDiff("m", geboorte, Date) + (Day(geboorte) > Day(Date))
     d = CInt(Date - WorksheetFunction.EDate(geboorte, maanden))
     Leeftijd = Int(maanden / 12) & " years, " & maanden Mod 12 & " months, " & d & " days"     'echte leeftijd

     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
          Leeftijd = Leeftijd & "|" & s
     End If
End Function

hello

thank you for this perfectly working function!
I would like to ask one more thing to adjust:
now the case -1 0 1 shows birthdays in the past / today / future.
could you adjust it so that it also says:
birthday two days ago ; birthday yesterday ; birthday tomorrow ; birthday day after tomorrow
(birthday -2 -1 0 1 2 and 3-30 in future/past (which is there already)

that would make my text complete ! thank you for your effort !!!

 

[BSALV]

     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

 

[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

thank you for the solution it works perfectly and it is soooooo much shorter then my formula hahaha ! have a great day !!!