Calculating the Difference Between 2 Dates

Hi There,

I need your help.

I can't seem to wrap my head around this:

I have the following code:

today = Format(Date, "dd/mm/yyyy")

MsgBox DateDiff("d", today, "04/02/2011")

and it returns 31 when it should be returning a date difference of 1.

I need the date in the following format dd/mm/yyy

Any help with this is greatly appreciated.

Thanks.

Jay

Hello Jay,

The problem is how VBA handles dates. The best thing to do is use unambiguous date formats. The date "04/02/2011" in UK date format is Feb. 04, 2011. In US date format it is April 02, 2011. The code below corrects the problem by converting the dates into numbers.

  Dim today As Variant, Date2 As Variant
  Dim X As Long

    Date2 = "04/02/2011"
    
    today = Split(Format(Date, "dd/mm/yyyy"), "/")
    today = DateSerial(CInt(today(2)), CInt(today(1)), CInt(today(0)))
    
    Date2 = Split(Date2, "/")
    Date2 = DateSerial(CInt(Date2(2)), CInt(Date2(1)), CInt(Date2(0)))
    
    X = DateDiff("d", today, Date2)