Help With DateDiff

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,470
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))
 

Some videos you may like

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
Joined
Jan 5, 2007
Messages
4,284
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
 

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,470
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
Joined
May 24, 2007
Messages
1,470
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
Joined
Jan 5, 2007
Messages
4,284
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
Joined
Jan 5, 2007
Messages
4,284
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
Joined
May 24, 2007
Messages
1,470
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
Joined
Nov 4, 2004
Messages
2,023
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
Joined
May 24, 2007
Messages
1,470
Hmm - these are the sample dates I had in my code:


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

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

Watch MrExcel Video

Forum statistics

Threads
1,096,426
Messages
5,450,353
Members
405,606
Latest member
greatness1987

This Week's Hot Topics

Top