# 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))``

#### 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

1,081,690
Messages
5,360,614
Members
400,592
Latest member

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...