View original (French)

VBA number of days between 2 dates

Solved
doom56530 Posted messages 13 Status Member -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
Hello,

I’m trying to calculate the difference between two dates using a macro for a database. I can calculate this difference on a single line. I’m using the following code:

Sub dif_date ()
Dim Dat1 as Date
Dim Dat2 as Date
Dim nbr_jr as Integer

Dat1 = Range("A1")
Dat2 = Range("B1")

nbr_jr = Dat2 - Dat1

Range("C1") = nbr_jr

End sub

So far it works as I want.

This calculation must be performed systematically (for each new entry) when I validate in a form.

I’m trying to create a loop but every time it crashes.

Sub dif_date ()
Dim Dat1 as Date
Dim Dat2 as Date
Dim nbr_jr as Integer
Dim i as Integer
For i = 1 to 20
Dat1 = Range("A" & i)
Dat2 = Range("B" & i)
nbr_jr = Dat2 - Dat1
Range("C" & i) = nbr_jr
Next i
End sub

If you could spot my errors or give me an idea to solve my problem that would be great.

Thanks in advance

Configuration:
Windows 2000 Firefox 3.5.7

2 answers

  1.
    Aligator_427 Posted messages 57 Status Member 21
     
    Note, you must ensure that your result 'nbr_jr' is positive; otherwise it will crash.

    PS: there is a ready-made function to calculate a difference between two dates: DateDiff("d",date1,date2)
    11
    1. Doudounet
       
      With the following construction: "&DateDiff("d","2016/01/16","2016/01/10")", it doesn't work. Why? Where is the error? Thanks.
      0
    2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Hello

      DateDiff("d", "2016 / 01 / 10", "2016 / 01 / 16")
      0
  2. doom56530 Posted messages 13 Status Member
     
    thank you, I tried with DateDiff and it works like a charm.
    0
    1. AMMOURI
       
      I can know why you put the "d" at the beginning
      0
    2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Hello,

      Excerpt from Excel online help yes, it’s free and useful

      DateDiff, function
      See also Example Particularities

      Returns a value of type Variant (Long) indicating the number of time intervals between two given dates.

      Syntax

      DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

      The syntax of the DateDiff function includes the following named arguments:

      Element Description
      interval String expression corresponding to the time interval used to calculate the difference between date1 and date2.
      date1, date2 Of type Variant (Date), represent the two dates used in the calculation.
      firstdayofweek Optional. Constant specifying the first day of the week. If no value is provided, the default is Sunday.
      Firstweekofyear Optional. Constant specifying the first week of the year. If no value is provided, the first week is the one containing January 1.



      Values

      The interval argument can take the following values:

      Value Description
      yyyy Year
      q Quarter
      m Month
      y Day of year
      d Day
      w Day of week
      ww Week
      h Hour
      n Minute
      s Second
      0