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

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
 

Forum statistics

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

Some videos you may like

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...
Top