DateDiff -- How to?

# Thread: DateDiff -- How to?

1. Junior Member
Join Date
Dec 1969
Posts
3

## DateDiff -- How to?

I need to compare two past dates and return values for the number of Years, Months, and Days between the two dates. The script below works if EndDate = Now, but returns information like: <BR><BR>1 y, 14 m, 32 d if the two dates are in the past (in example, StartDate = 3/2/1944 and EndDate = 6/4/1946).<BR><BR>Obviously, months have no more than 31 days, and years have no more than 12 months.<BR><BR>How can I fix this? I&#039;m tearing my hair out! Thanks for any help!<BR><BR>--Sean<BR><BR><BR>&#039; EndDate is fieldvalue for end date of term<BR>EndDate = (Recordset1.Fields.Item("end").Value) <BR><BR>&#039;StartDate is fieldvalue for beginning of term<BR>StartDate = (Recordset1.Fields.Item("start").Value)<BR><BR>Yea rsLeft = DateDiff("yyyy",StartDate,EndDate) -1<BR>StartDate = DateAdd("yyyy",YearsLeft,StartDate)<BR>MonthsLeft = DateDiff("m",StartDate,EndDate) -1<BR>StartDate = DateAdd("m",MonthsLeft,StartDate)<BR>DaysLeft = DateDiff("d",StartDate,EndDate) - 1<BR>StartDate = DateAdd("d",DaysLeft,StartDate)<BR>%&#062;

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Not a bad question!

An easy way would be to follow up all that code with:<BR><BR>&#060;%<BR>If DaysLeft &#062; 30 Then<BR>&nbsp; &nbsp; DaysLeft = DaysLeft - 31<BR>&nbsp; &nbsp; MonthsLeft = MonthsLeft + 1<BR>End If<BR>If MonthsLeft &#062; 11 Then<BR>&nbsp; &nbsp; MonthsLeft = MonthsLeft - 12<BR>&nbsp; &nbsp; YearsLeft = YearsLeft + 1<BR>End If<BR>%&#062;<BR><BR>But that won&#039;t be accurate if the starting month had fewer than 31 days, just to pick an example. [But maybe it is accurate enough for your purposes? If so, use it and be done?]<BR><BR>I think, if you want to do this "right", you have to abandon VBS&#039;s DateDiff function. <BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
3

## Good Idea!

Hey, thanks! Excellent idea--that will probably get me close enough. <BR><BR>Hmmm.... I know the starting month for each date, so I could probably work that problem out--that would only leave the leap year problem, which is probably already figured into the day count. <BR><BR>I would like to be able to estimate what the margin of error is, though it&#039;s obviously exponential and likely fairly inconsequential over a century. Dates begin in the 1940s. <BR><BR>Thanks,<BR><BR>--Sean

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Another concept...

Beginning with the DAY of the start date, advance by days until you match the DAY of the end date.<BR><BR>Beginning with the MONTH-DAY you ended up that way, advance by months until you match the MONTH-DAY of the end date.<BR><BR>Beginning with the MONTH-DAY-YEAR you ended up with in step 2, advance by years until you match the full end date.<BR><BR>Example: 2/23/1944 --&#062;&#062; 2/12/1949<BR><BR>23 --&#062;&#062; 12. Since Feb 1944 is a leap year, that means you go 23--&#062;&#062;29 and then 1--&#062;&#062;12. 6+12 = 18 days. In the process you moved from February to March.<BR><BR>Now you are at, effectively, 3/12/1944 --&#062;&#062; 2/12/1949<BR><BR>You have to advance from March to December and then January to February. You advance 11 months. In the process, you moved forward a year.<BR><BR>Now you are at, effectively, 3/12/1945 --&#062;&#062; 2/12/1949<BR><BR>Advance 4 years.<BR><BR>Total elapsed time: 4 years, 11 months, 18 days.<BR><BR>Which is right. If you just look at the original dates, in your head you&#039;d probably say "Why, that&#039;s 11 days short of exactly 5 years." And, since Feb. 1944 has 29 days, 11 days short of that is, indeed, 4 years 11 months 18 days. Yes?<BR><BR>So the only tricky part of this, really, is advancing the start DAY to match the end DAY and making sure you go to the correct end of the start MONTH (that is, get the number of days in that month right).<BR><BR>I know how to do that, but I&#039;ll leave that as an exercise for you (besides, you might figure out a shorter way than my idea!) unless you ask for help.<BR><BR>One problem: Suppose you are asked to go <BR>&nbsp; &nbsp; 2/20/1945 --&#062;&#062; 8/31/1945<BR><BR>Ummm...how do you even *get* to 2/31/1945??? <BR><BR>*You* will have to decide what to do in a case like that, where the ending day is after the last day of the starting month. There is certainly no "correct" answer...you&#039;ll just have to make an arbitrary decision and stick with it.<BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
3

## RE: Another concept...

Brilliant! I&#039;ll give it a try. Accuracy is important, since the data compares the lengths of terms of third world leaders. The script sounds advanced. Would you be willing to review a few attempts?<BR><BR>I&#039;ll happily post my email address.<BR><BR>Thanks,<BR><BR>Sean

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## My email address is pretty public...

...plastered all over the ASPFAQs, for example.<BR><BR>But if you want me to see it this weekend, better use junco.junction@verizon.net<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•