Date Difference (1 Viewer)

[access09]

Hey,

I am trying to count the number of days between two dates. The dates are associated with an insurance policy. One date is for the day the insurance policy began and the other date is for the day the insurance policy was renewed.

What I want to know is the diference in days of the date in the month the policy was renewed and forget about the year.

For example: First Date - 12092005 (stands for 12/09/2005) Second Date - 15092006 (stands for 15/09/2006)

So what I want is the difference between 12/09 and 15/09. So the answer would be three days.

I used the left function to get rid of the year bit from the date. So the dates would be '1209' and '1509'

Anybody have any ideas? I tried the following query:

Expr3: DateDiff('d',[Expr1],[Expr2])

The answer it gives is -300

 

[Dennisk]

why did you remove the year, once you did that technically speaking you dont have a date. DateDiff requires a full date dd mm yyyy.

 

[JANR]

DateDiff requires a full date dd mm yyyy.

Not quite, from Immediatewindow:

?datediff("d",#12/30#,#1/2#)
-362

?datediff("d",#12/30/2008#,#1/2/2009#)
 3

But the single quote around d prompted an error. ??

JR

 

[access09]

Made changes to my post, see above.

I leave out the year because I only want to know which insurance policies dont renew on the same date of the month they were started regardless of the year and to show the difference in days.

 

[access09]

The date difference function should still work if I just have the day and month. But all my insurance policies have their dates in the format '1209' which should be '12/09'. Can you think of a way I could insert '/' into the '1209' date field?

 

[Brianwarnock]

The best way to do this is to use dateserilal to build the 2 dates with the same year then datediff will indicate by sign as to whether the renewal is early or late.

Dateserial(year(date()),mid(yourfield,3,2),left(yourfield,2))

Brian

 

[JANR]

Since you have a "dateformat" which isen't US standard you must flip 1209 to 0912 before using datediff.

A custom function could do it if you can't reformat the fields to a date/time format.

Option Compare Database
Option Explicit
Public Function compDate(iDATE As String, oDATE As String) As Variant
    Dim USiDate As Variant
    Dim USoDate As Variant
 
    USiDate = Right(Left(iDATE, 4), 2) & Left(iDATE, 2)
    USoDate = Right(Left(oDATE, 4), 2) & Left(oDATE, 2)
    compDate = DateDiff("d", USiDate, USoDate)
End Function

You can call this function from a query and feed it the iDATE (startDate) and oDATE (endDate) as strings and the return will be the diffrence in the dates.

from Immediate window:

?compDate("12092005","15092006")
 3

JR

 

[Brianwarnock]

I've never converted my UK date format to US to use Datediff.

Brian

 

[JANR]

On my "scandianvian" system I had to or it woulden't compute , I must also use ; in stead of , in expression which is a pain to remember.

JR

 

[Brianwarnock]

It is only when hardcoding dates that it becomes an issue in VBA and SQL ie #01/23/2009# instead of #23/01/2009#

Brian

 

[JANR]

It is only when hardcoding dates that it becomes an issue in VBA and SQL ie #01/23/2009# instead of #23/01/2009#

That's why dates/time is so difficult to get your head around, and often causes problems for developers

But the dateserial solution you came up with and properly format the field to datefield would benefit the OP in the longrun, to avoid future headaces.

JR

 

[access09]

Thanks Brian. The DateSerial function worked a charm. Funny enough it did come out in the American date format but that doesnt matter.

It works for all the dates but it doesnt count the way I want it too between certain months.

For example,

Start Date - 05/12/09
Renewal Date - 10/01/10

Even after the SerialDate converts it to the same year it gives a difference of 329 days. But it should only give a difference of 36 days. This happens to all dates that run from the latter months into a new year.

Any ideas?

 

[Brianwarnock]

Without doing the arithmetic isn't that answer ,329, correct, the renewal date should have been 5/12/10, so if they renewed on 10/1/10 they were way out.

Brian