| Author |
Message |
Zuzanna
Groupie
Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
|
   Topic: Date conversion
Posted: 11 Oct 2011 at 10:24am |
Hi,
I have a formula as follows
ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-{SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY})/365* {@BASEHOURS},2) This formula should subtract 2 dates (EXP9_2 is a prompt date eg. Oct-1-2011) followed by the other calculation. The problem is that, sometimes the second date LHS_DT_ORIG_SENRTY is null and the result is blank. In this case I want the calculation to use Oct-1-2011 div 365 mult base hrs. I tried to use the nvl function in the SQL query nvl(E.LHS_DT_ORIG_SENRTY, '0000/00/00') thinking that it will use zero in crystal formula, but it doesn't work, result blank. I also tried to use isdate({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) to test for null, but it's giving me formula error saying that is should be a string.
Can anyone help ? Thanks in advance.
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Oct 2011 at 10:48am |
maybe...
Round({SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}- (ifisnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then date(2011,10,1) else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}))/365* {@BASEHOURS},2)
Edited by DBlank - 11 Oct 2011 at 10:48am
|
|
IP Logged |
|
Zuzanna
Groupie
Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
|
Posted: 13 Oct 2011 at 4:51am |
Hi,
I didn't explain it properly. What I really want is that if in the following formula
ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-{SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY})/365* {@BASEHOURS},2)
the LHS_DT_ORIG_SENRTY is null, I want to use zero in the subtraction from EXP9_2. So if the EXP9_2 prompt date equals to eg.Oct-1-2011 and the LHS_DT_ORIG_SENRTY is null, I want to subtract 0 so the result of the subtraction is whatever date is in EXP9_2.
Also I tried the suggested method, but I keep getting syntax error
"The ) is missing" and it highlights the ifisnull. I tried to fix it, but it didn't work.
Thanks.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Oct 2011 at 5:07am |
missed a space in the ifisnull, should be "if isnull".
I assumed you were using date fields but now it looks like numeric fields.
IF that is correct you can either chaneg the fomrual to use defualt values for nulls (pick list option in the formula manager or chaneg the formula as
ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-(if isnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then 0 else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY})/365* {@BASEHOURS},2)
|
|
IP Logged |
|
Zuzanna
Groupie
Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
|
Posted: 13 Oct 2011 at 5:22am |
Hi,
now I am getting an error "A number is required here"
ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-(if isnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then 0 else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}))/365* {@BASEHOURS},2)
EXP9_2 is a date and {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY} is also a date.
I don't know how to get around the formula to calculate the balance if LHS_DT_ORIG_SENRTY date fiels is null.
Thanks.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Oct 2011 at 5:43am |
I thought these were dates,
So you want the date difference in days between EXP9 and LHS_DT_orig_sentry, correct?
if the LHS_DT_orig_sentry is NULL you can't use zero. So do you want the days from today? or alwasy 0 for the whole thing? or seomething else?
|
|
IP Logged |
|
Zuzanna
Groupie
Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
|
Posted: 13 Oct 2011 at 8:48am |
Hi,
I see what you mean, if I have 2 dates
EXP9 - 2011/10/04 and LHS_DT_orig_sentry - 2006/10/02 and I subtract them in the formula as dates and display the result on the report, I get date difference 1,828. I can do the same manually in excel by converting 2011/10/04 to number 40,820 and 2006/10/02 to number 38,992. I think excel does the conversion starting with Jan/1/1900. But if LHS_DT_orig_sentry is blank, I just want to use the 2011/10/02 converted to number 40,820, so I can use it in my balance calculation. Can you convert date to a number in crystal or is there another solution ?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Oct 2011 at 9:23am |
I think you want to use today if it isnull.
if you jsut used the numeric day value it is not coming close to any comparitive value of you date difference.
try it and see if you get tha values you expected...
Round({SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-(if isnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then currentdate else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}))/365* {@BASEHOURS},2) |
|
IP Logged |
|
Zuzanna
Groupie
Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
|
Posted: 14 Oct 2011 at 8:29am |
Hi,
luckily it turned out to be invalid data issue. Thanks for all your guidance.
|
|
IP Logged |
|
|
|
