CODEWITHSUNDEEP
excelvbadatevariablesdatediff
maxim465
maxim465

Reputation: 195

Difference of dates

There are two dates(one over the second). I want to know the difference between them (in days).Programm produces the value “0”. Error in the variables vat1,vat2. Tell me , how to set them correctly ?

   Sub ógv()
   Dim dat1 As Date
   Dim dat2 As Date
   Dim vat1 As Date
   Dim vat2 As Date
   Dim Day1 As Long
   Dim Month1 As Long
   Dim Year1  As Long
   Dim Day2 As Long
   Dim Month2 As Long
   Dim Year2  As Long
   Dim C As Range

   Set C = ThisWorkbook.ActiveSheet.Range("C35")
   '1
   dat1 = C.Value
   Day1 = DatePart("d", dat1)
   Month1 = DatePart("m", dat1)
   Year1 = DatePart("yyyy", dat1)
   vat1 = Month1 / Day1 / Year1
   '2
   dat2 = C.Offset(-1, 0).Value
   Day2 = DatePart("d", dat2)
   Month2 = DatePart("m", dat2)
   Year2 = DatePart("yyyy", dat2)
   vat2 = Month2 / Day2 / Year2


    Dni = DateDiff("d", vat2, vat1)
     MsgBox Dni

  End Sub

Upvotes: 0

Views: 144

Answers (3)

Variatus
Variatus

Reputation: 14383

You are making your life unnecessarily difficult. All dates are represented by numbers like 43218.75 where the integer defines the day and the decimals the time. In this example, 0.75 days have lapsed since midnight on day 43218 (April 28, 2018). Therefore 43218.75 stands for 6:00PM on Apr 24, 2018. It follows that 43210.75 would be 8 days earlier and, therefore, 43218.75 - 43210.75 would be the date difference in days. Use the Int() function to extract the date from a Date/Time value.

The CDate() function extracts a date from either a string or a number. Therefore if your range C35 holds a true date (as a number) or a string, like "24/4/2018" CDate(Range("C35").Value) will return a number which you can compare with another number similarly created. Hence the following code.

Private Sub TestFunction()

    MsgBox "Date difference is " & ogv("C35") & " days."
End Sub

Function ogv(CellAddress As String) As Integer

    With ThisWorkbook.ActiveSheet.Range(CellAddress)
        ogv = Abs(Int(CDate(.Value)) - Int(CDate(.Offset(-1).Value)))
    End With
End Function

Use the Sub to test call the function. Use the code in the sub in your VBA project.

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21657

You could use the DateDiff function.

DateDiff( interval, date1, date2 )

  • interval :String expression that is the interval of time you use to calculate the difference between date1 and date2.
  • date1, date2 : Two dates you want to use in the calculation.

The interval argument has these settings:

Setting  Description
-------  -----------
 yyyy    Year
 q       Quarter
 m       Month
 y       Day of year
 d       Day
 w       Weekday
 ww      Week
 h       Hour
 n       Minute
 s       Second

Full Documentation here.

Upvotes: 0

jsheeran
jsheeran

Reputation: 3037

The line

vat1 = Month1 / Day1 / Year1

doesn't do what you expect. Instead, it divides Month1 / Day1 by Year1.

It looks like C.Value and C.Offset(-1, 0).Value are both valid dates. You should be able to use those as the arguments to DateDiff.

Upvotes: 1

Related Questions