# Help With DateDiff

#### Evagrius Ponticus

##### Well-known Member
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

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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Lewiy

##### Well-known Member
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:
Rich (BB code):
Rich (BB code):
Rich (BB code):
``Cells(MyRow, MyCol).Value = DateDiff("d", Cells(MyRow, "S"), Cells(15, MyCol)) – 1``

#### cornflakegirl

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

#### Evagrius Ponticus

##### Well-known Member
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

##### Well-known Member
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.

Last edited:

#### Lewiy

##### Well-known Member
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

##### Well-known Member
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:
Rich (BB code):
Rich (BB code):
Rich (BB code):
``````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

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

#### cornflakegirl

##### Well-known Member
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.

Last edited:

#### Evagrius Ponticus

##### Well-known Member
Hmm - these are the sample dates I had in my code:

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

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