Help With DateDiff

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467
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))
 
Rich (BB code):
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
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
-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().
 
Upvote 0
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,
 
Upvote 0
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.
 
Last edited:
Upvote 0
I think this works:

Code:
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
 
Upvote 0
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,
 
Upvote 0
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") :oops:
 
Upvote 0
Sorry to be dense Emma, where would I make the change?

Code:
  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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top