Help With DateDiff

[Evagrius Ponticus]

Hi,

I am not sure if DateDiff can do this. The code below give me 7. But I want the answer to include only full months - not partial months. I know that can be done in Excel using the Datedif, but not sure if it is possible using the VB Datediff.

So I would like 6 to be the answer below because there are only 6 full month between the two dates.


cells(MyRow, "s") = 8/31/2013

cells(15,Mycol) = 3/2/2014

 Cells(MyRow, MyCol).Value = DateDiff("d", Cells(MyRow, "S"), Cells(15, MyCol))

 

[Lewiy]

I believe that DateDiff deals with months slightly differently, so will always give you a figure one month higher that what you want. So just subtract one in your formula:

Cells(MyRow, MyCol).Value = DateDiff("d", Cells(MyRow, "S"), Cells(15, MyCol)) – 1

 

[cornflakegirl]

Lewiy - won't this break if an exact number of months has elapsed?

 

[Evagrius Ponticus]

I see, thanks Lewiy. If I subtract 1, then I get the correct answer of 6. My next challenge would be how do I get the number of days between the months exluding years and months. So in example I gave below the answer would be 2 - from the 31 to the 2. I can do this in Excel but not sure how it's done with Datediff. Do you have a suggestion?

Thanks,

 

[Evagrius Ponticus]

Right! From 1/1/2008 to 5/1/2008 I get "4". Sorry - 4 is correct . . .but if I -1 then I get 3 which wouldn't be correct. So I guess it does break with even months.

 

[Lewiy]

I see, thanks Lewiy. If I subtract 1, then I get the correct answer of 6. My next challenge would be how do I get the number of days between the months exluding years and months. So in example I gave below the answer would be 2 - from the 31 to the 2. I can do this in Excel but not sure how it's done with Datediff. Do you have a suggestion?

Thanks,

Try this:

Cells(MyRow, MyCol).Value = DateDiff("d", DateSerial(1, 1, Day(Cells(MyRow, "S"))), DateSerial(1, 1, Day(Cells(15, MyCol))))

 

[Lewiy]

Lewiy - won't this break if an exact number of months has elapsed?

Good catch!!
Yes, it will break if the days of the month are the same, but you can build in an if statement to deal with that:

If Day(Cells(MyRow, "S")) = Day(Cells(15, MyCol)) Then<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
    'don't subtract 1<o:p></o:p>
    Else: 'do subtract 1<o:p></o:p>
End If

 

[Evagrius Ponticus]

Lewiy, I ran your code and the result was -29 for the example below . . -29 days doesn't make sense to me??

 

[cornflakegirl]

Try this:

Cells(MyRow, MyCol).Value = DateDiff("d", DateSerial(1, 1, Day(Cells(MyRow, "S"))), DateSerial(1, 1, Day(Cells(15, MyCol))))

I get -29...

xpost - it does make sense - because 01/31/01 is 29 days after 01/02/01. The code needs a test to see if the day value of cell1 is bigger than the day value of cell2.

 

[Evagrius Ponticus]

Hmm - these are the sample dates I had in my code:


cells(MyRow, "s") = 8/31/2013

cells(15,Mycol) = 3/2/2014