Calculation between Numbers and Dates

[Rbp1226]

Using CR 11 & Oracle 10g

I have a report need calculations between Numbers and Dates, I could not get correct result, is any one can help? Thank you!!

Simple example below:

Column 1 Column 2 Column 3
200801 01/23/1948 Mike
200801 5/27/1959 Bob

The result should be Mike = 60 years old, Bob = 49 years old

Rbp

 

[dgillz]

Bob isn't 49 yet, won't be until 5/27. Please explain.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[Rbp1226]

Hello Dgillz,

You are right, Bob isn't 49 yet, I am still have no good idea for the caculation? Thanks!!

Rbp

 

[Rbp1226]

Hi dgillz
I am using CRXI Release 1

Thank You!!

Rbp

 

[dgillz]

Well you need to convert 200801 to a date. What date is this supposed to be?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[dgillz]

Once you determine how to express 200801 as a date, try a formula like this:

if Month({Birthdate}) < Month(CurrentDate) or
(Month({Birthdate)) = month(CurrentDate) and
Day({Birthdate)) <= Day(CurrentDate)))
Then Year(CurrentDate) - Year({Birthdate})
Else Year(CurrentDate) - Year({Birthdate}) - 1

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[Rbp1226]

Hi dgillz,

This is monthly report, yes, 200801 need convert number to date, DOB is date type. Thanks.

Rbp

 

[dgillz]

So what date does 200801 represent?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

You need to clarify how the birthdate should relate to the month. With your example 200801, what if someone's birthday was during January? In other words, would you want in this example to use January 1 or January 31 to determine the age?

-LB

 

[beltmanjr]

I'm certain that the following should fix your issue. However, you do need to decide on the issue raised by lbass.

datediff('yyyy',{Birthdate}, cdate(tonumber(left({Column1},4)),tonumber(mid({Column1},5,2)),XX))

{Column1} refering to Column1 as mentioned in your first post
XX the number as raised by lbass

 

[lbass]

Well, no, beltmanjr, that wouldn't work, regardless of the value of "XX", since datediff simply subtracts the years in the dates. You would have to use an age formula like Ken Hamady's:

WhileReadingRecords;
DateVar Birth:= {ages.Birth}; // Replace this with your field for Date Of Birth
DateVar Ann := {ages.DateAnn}; // Replace this with CurrentDate to get their age as of the time of the report
//or the date field of an event to get their age as of the time of that event.
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

-LB

 

[Rbp1226]

Thank you all,

This is monthly report, 200801 express January 31, 2008 data, and 200802 express February 28(9), 2008 data…and so on. First I need to convert this number to date, and then subtract DOB to get age. I am still trying to work out.

Thank you!

Rbp

 

[lbass]

Use this, substituting your actual field names:

WhileReadingRecords;
DateVar Birth:= {table.dob}; // Replace this with your field for Date Of Birth
DateVar Ann := dateserial(val(left(totext({table.yrmo},0,""),4)),val(right(totext({table.yrmo},0,""),2))+1,1)-1;//gives last day of month
//or the date field of an event to get their age as of the time of that event.
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

-LB

 

[beltmanjr]

Well, no, beltmanjr, that wouldn't work, regardless of the value of "XX", since datediff simply subtracts the years in the dates. You would have to use an age formula like Ken Hamady's:

Ah, you're 100% correct! The datediff simply takes the 2 year parts and subtracts.

 

[Rbp1226]

Hi LB,

The formula works well, Thank you so much! Thank you all your help guys!

Rbp