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

 

[cornflakegirl]

If Day(Cells(15, MyCol)) >= Day(Cells(MyRow, "S")) Then
     Cells(MyRow, MyCol).Value = DateDiff("d", DateSerial(1, 1, Day(Cells(MyRow, "S"))), DateSerial(1, 1, Day(Cells(15, MyCol))))
Else
     Cells(MyRow, MyCol).Value = DateDiff("d", DateSerial(1, 1, Day(Cells(MyRow, "S"))), DateSerial(1, 2, Day(Cells(15, MyCol))))
End If

 

[Lewiy]

-29 would be correct as that is the difference in the days if you ignore the month and year. As Emma has suggested, if you only want positive differences then test for whether the subtracted day is larger or smaller. Alternatively you could use an absolute value (ignores positive/negative). For this, enclose your formula within Abs().

 

[Evagrius Ponticus]

Lewiy - maybe I can explain myself better. The difference between 8/31/2013 and 3/2/2014 is 6 months and 2 days, right?

The formula I need has to give me exactly that, 6 and 2. I was thinking of concantinating two formula's or codes, one for the 6 and one for the 2. You solved the 6 portion by suggesting subtracting 1 from the original code, but first test to make sure the months aren't even. The 2 is what I am struggling with now. Hope that makes sense. Thanks,

 

[cornflakegirl]

BlackTiger - my amendment to Lewiy's code for days will give you 2 for your test dates.

EDIT - just thought - if you want the difference between 26 Feb 2008 and 4 April 2008, you want 1 month 7 days. But Lewiy's / my method will give 1 month, 9 days. Needs a rethink.

 

[cornflakegirl]

I think this works:

If Day(Cells(15, MyCol)) >= Day(Cells(MyRow, "S")) Then
     Cells(MyRow, MyCol).Value = DateDiff("d", DateSerial(1, 1, Day(Cells(MyRow, "S"))), DateSerial(1, 1, Day(Cells(15, MyCol))))
Else
     Cells(MyRow, MyCol).Value = DateDiff("d", DateSerial(Year(Cells(MyRow, "S")), Month(Cells(MyRow, "S")), Day(Cells(MyRow, "S"))), DateSerial(Year(Cells(MyRow, "S")), Month(Cells(MyRow, "S")) + 1, Day(Cells(15, MyCol))))
End If

 

[Evagrius Ponticus]

Sorry Emma - I missed that post. But I tested it and IT WAS PERFECT! I am not usre how it works but I'll figure it out and hopefully learn. Thanks both of you for the hand - it may have taken me a while to figure it out alone. Thanks,

 

[cornflakegirl]

Um... just realised that I've used

DateSerial(Year(Cells(MyRow, "S")), Month(Cells(MyRow, "S")), Day(Cells(MyRow, "S")))

You may want to replace this with

Cells(MyRow, "S")

 

[Evagrius Ponticus]

Sorry to be dense Emma, where would I make the change?

  If Day(Cells(15, MyCol)) >= Day(Cells(MyRow, "S")) Then
            Cells(MyRow, MyCol).Value = Cells(MyRow, MyCol) & "m. " & DateDiff("d", DateSerial(1, 1, Day(Cells(MyRow, "S"))), DateSerial(1, 1, Day(Cells(15, MyCol)))) & "d"
            Else
            Cells(MyRow, MyCol).Value = Cells(MyRow, MyCol) & "m. " & DateDiff("d", DateSerial(1, 1, Day(Cells(MyRow, "S"))), DateSerial(1, 2, Day(Cells(15, MyCol)))) & "d"
            End If

 

[cornflakegirl]

You first need to use the code in post #15 - make the replacement within that.

 

[Evagrius Ponticus]

LOL - thanks again - didn't catch the update