DateDiff

I am trying to get total date difference between two dates in crystal formula
for example if i enter

Date1 17/03/2006
Date 2 06/01/2005

The need the result should be like 1 year 2 months ...days etc.,

I used this formula for getting the difference in years
But now i want the full difference in months and days

DateDiff ('yyyy',{Date1},{Date2})

Any ideas please
Thanks

Quote:

---

Originally Posted by wizkid

I am trying to get total date difference between two dates in crystal formula
for example if i enter

Date1 17/03/2006
Date 2 06/01/2005

The need the result should be like 1 year 2 months ...days etc.,

I used this formula for getting the difference in years
But now i want the full difference in months and days

DateDiff ('yyyy',{Date1},{Date2})

Any ideas please
Thanks

---

i am not sure on this but i believe days would be:

VB Code:

DateDiff (d,{Date1},{Date2})

and months would be

VB Code:

DateDiff (m,{Date1},{Date2})

Hi :wave:
I do not know that what reporting version you are using but
Crystal Reports 8 added some new Date manipulation formulas. The DateDiff function has two forms:

Quote:

---

DateDiff (intervalType, startDateTime, endDateTime)
DateDiff (intervalType, startDateTime, endDateTime, firstDayOfWeek)

---

The first can be used to find the number of intervals between two dates. The IntervalType can be "yyyy" (Years), "q" quarters, "m" (months), "d" (days). See the Crystal Reports help for other variations and some useful examples.

The second form can be used to count a specific day between two dates. It uses the "ww" IntervalType to find the number of the first day of the week.

Quote:

---

DateDiff ("ww", {table.datefield1}, {table.datefield2}, crWednesday)

---

will tell you how many Wednesdays there are between two dates
for more detail click on the crystal report website
http://support.businessobjects.com/ :thumb:

Thank you very much for your reply.
I am using CR9.
I can get the days and months with different formula
usiing 'yyyy' and 'm' in interval type.
But what i am asking is i want the entire difference in one formula

For example if i give two dates

Date1: 17/03/2006
I need the result should be like 1 year 2 months ...days etc., in one formula

for example if you give

datediff('yyyy',date1,date2) --->the result will be only in years

datediff('m',date1,date2)---> the result will be only in months

etc...


The need the result should be like 1 year 2 months ...days etc., in one formula


Any idea? :confused:

Any idea please?
Thanks

Here we go,

cstr(DateDiff ("yyyy", #10/7/1999#, #2/10/2005#))+' year '+cstr(DateDiff ("m", #10/7/1999#, #2/10/2005#))+' month '

My problem is i want the difference between two dates
in days months and year

For example if the two dates are 1/12/2006,3/02/2006

Then the output should be

0 years 2 months 2 days

Here's a function I wrote in VB6 ages ago. By using Crystal's Basic syntax in the formula editor you may be able to duplicate its functionality. Good luck, I'm not assuring that it's bug-free.

VB Code:

Public Function IntervalBTDates(ByVal StartDate As Date, ByVal EndDate As Date) As String
    '
    '   Returns the interval between 2 dates in years, months & days
    '
    Dim dd As Long
    Dim mm As Long
    Dim yy As Long
    
    Dim ret As String
    Dim tmpDate As Date
    '
    '   Do some basic validation before going any further
    '
    If StartDate > EndDate Then
        MsgBox "Start date must be less than the end date.", vbExclamation, "Date Error"
        Exit Function
    ElseIf StartDate = EndDate Then
        IntervalBTDates = "0 years, 0 months, 0 days"
        Exit Function
    End If
    
    dd = DateDiff("d", StartDate, EndDate)
    mm = DateDiff("m", StartDate, EndDate)
    If mm > 0 Then
        If Day(StartDate) > Day(EndDate) Then
            mm = mm - 1
        End If
    End If
    
    yy = mm \ 12
    mm = mm Mod 12
    
    tmpDate = DateAdd("yyyy", -yy, EndDate)
    tmpDate = DateAdd("m", -mm, tmpDate)
    dd = Abs(DateDiff("d", StartDate, tmpDate))
    
    If yy = 1 Then
        ret = "1 year, "
    Else
        ret = CStr(yy) & " years, "
    End If
    
    If mm = 1 Then
        ret = ret & "1 month, "
    Else
        ret = ret & CStr(mm) & " months, "
    End If
 
    If dd = 1 Then
        ret = ret & "1 day"
    Else
        ret = ret & CStr(dd) & " days"
    End If
 
    IntervalBTDates = ret
    
End Function

Thanks pnish
I will try this one in crystal

Hi wizkid
i have same problem. what u did for it in crystal report.did u try pnish method in crystal report

Roshani