Home Difference of dates
Reply: 3

Difference of dates

maxim465 Published in 2018-01-09 08:10:10Z

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")
   dat1 = C.Value
   Day1 = DatePart("d", dat1)
   Month1 = DatePart("m", dat1)
   Year1 = DatePart("yyyy", dat1)
   vat1 = Month1 / Day1 / Year1
   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
jsheeran Reply to 2018-01-09 08:22:21Z

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.

ashleedawg Reply to 2018-01-09 08:23:39Z

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.

Variatus Reply to 2018-01-09 09:04:40Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.301217 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO