Date Diff

[gingerbagpuss99]

I am new to formulas and am trying to work out the difference in months and days between 2 dates.

I have tried datediff ("d", start date, end date) but it doesn't return the correct number of days per unit.

Thanks in advance

Sarah

 

[MJRBIM]

The datediff ("d", start date, end date) formula should have returned the correct number days of between the dates.

Please post some examples of the StartDate and EndDate data, and the incorrect number of days that are being returned.

 

[gingerbagpuss99]

On looking at it again, it did return the correct number (the field was partially hidden!), but I was encouraged I was on the right track. My problem now is to get it to show the result in YYYY MM DD. I can get then to show separately but can't show them together. I think I might need to do separate formulas for each and concantonate but I get am a bit lost as where start.

Thanks again

 

[MJRBIM]

Please post some examples of the StartDate and EndDate data, and a sample of the formatted returned you want.

 

[gingerbagpuss99]

This is my formula

datediff ("d", {Referral_details.Date_Referred}, {Patient.Date_Death})

Example data:

Ref date 4.4.2000 RIP date 13/7/2000 result: 100
Ref date 17/4/2000 RIP date 12/5/2000 result: 25
Ref date 23/6/2000 RIP date 3/1/2004 result: 1,289

I would like the return result to show number of years, months and days between Ref date and RIP date.

Thanks

 

[Madawc]

For an aproximate answer, divide first by 365 and then 30 and use remainders.

There are also year and month options with datediff.

Madawc Williams (East Anglia)

 

[gingerbagpuss99]

Thanks. When I put "YYYY, MM, DD" as the first argument in date diff I get an error "Argument #1 of date diff is out of date range". I can get them separately but not together which I what I am trying to do. I am quite a novice so please forgive me if it is simple.

 

[Madawc]

Crystal 8.5 lets you find the difference as years or months or days; they saw no need for a command that would do all three together. You'll have to stitch together such a function yourself.

Madawc Williams (East Anglia)