Date diff between date and julian date

[cjbrown815]

Hello,
I have a report that calcualtes the difference between 2 date fields. SAMPLE.DATE1 and SAMPLE.DATE3. Some of our other products do not use the SAMPLE.DATE1 field but instead go by the julian date that is part of the Quality_Code {SAMPLE.TEXT3}string. So I need to take the first 3 numbers of the Quality_Code, figure out where that falls on the calendar and then calculate the difference between that date and the SAMPLE.DATE1.

Current Formula
datetimevar d1:={SAMPLE.DATE3};
datetimevar d2:={SAMPLE.DATE1};
totext(Datediff("n",d2,d1)/60 /24,0,"") & " days"

Quality_Code {SAMPLE.TEXT3} examples
189-08
203-07

The quality code is: julian date-YY

Is this even possible?

thanks,

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[CoSpringsGuy]

are you sure that is a julian date or is it

number of days in the year-year?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[cjbrown815]

Isn't that what a julian date is?

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

You are correct. It is number of days in the year-year

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[CoSpringsGuy]

ok i have a formula for you but i am right in the middle of something .. Ill send it in a bit if someone doesnt get to it first

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

Here is a formula that will convert string field "198-08" to its corresponding date format of 7/7/2008

date(tonumber("20"&split({@juliandate},"-")[2]),1,1)
+
(tonumber(split({@juliandate},"-")[1])-1)

You should be able to plug that into your datediff formula to get the desired results.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[cjbrown815]

Sorry I dont understand.

The current formula is working. How can I expand to include your formula so if the condition is correct it will calculate the julian date versus the standard date fields?

Most of the time its the difference between to date fields. But rarely it's the difference between a date field and the numeric value entered in the text field. i.e. 184-08.

Will one formula handle all of this?

thanks for your help.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[CoSpringsGuy]

When you have to use the nonnumeric text field you refer to what does the field {sample.date1} contain? is it null?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

if it is null then try this
datetimevar d1:={@datefield2};
datetimevar d2:={@datefield1};
datetimevar d3:=date(tonumber("20"&split({@juliandate},"-")[2]),1,1)
+(tonumber(split({@juliandate},"-")[1])-1);

if isnull({@datefield1}) then
totext(Datediff("n",d3,d1)/60 /24,0,"") & " days"
else
totext(Datediff("n",d2,d1)/60 /24,0,"") & " days"


if i misunderstood you and you need to completely replace d2 with the julian date field then try this
datetimevar d1:={@datefield2};
datetimevar d2:=date(tonumber("20"&split({@juliandate},"-")[2]),1,1)
+(tonumber(split({@juliandate},"-")[1])-1);


totext(Datediff("n",d2,d1)/60 /24,0,"") & " days"

And if I am completely misunderstanding your problem post some more and I will see if I can figure it out.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[cjbrown815]

Thank you,
The DATE1 field is the production date. The DATE3 field is the survey date. We want to know the age of the product when it is surveyed. Some products have the production date as when it was made and some have the production date as when it was package. The latter also uses the TEXT3 field to enter the production code Julian-YY. The DATE1 field is still populated but because it is the package date I need to refer to the code to get the true age of the product.
Our production facilities submit samples using the production date and that's correct. Our packaging and distribution center enters their own production date (when package) but also uses the julian code that came with the product and that's the true age.

So no, the DATE1 field is never NULL

Thanks again. A little confusing I know.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[CoSpringsGuy]

hmmm ok well the last formula I sent you is basically trying to determine when you want to use the julian date in the production code field instead of the date in datefield1. That If statement can be changed to something telling the formula which date you want to use. Before this thread I had never tried to convert julian-yy to a date but I tested it several ways before I posted it and it works. The problem is now telling the formula which date to use I think.

If I read your description correctly, Text3 would be blank or null if the product has the production date as when it was made.
so maybe this would work

datetimevar d1:={@datefield2};
datetimevar d2:={@datefield1};
datetimevar d3:=date(tonumber("20"&split({text3},"-")[2]),1,1)
+(tonumber(split({text3},"-")[1])-1);

if isnull({text3) then // change to if trim({text3}) = "" then if its sending back a blank field which is probably not the case
totext(Datediff("n",d2,d1)/60 /24,0,"") & " days"
else
totext(Datediff("n",d3,d1)/60 /24,0,"") & " days"



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[cjbrown815]

thank you for your help,
Unfortunately I can't test this formula out until Monday.

None of the fields are ever NULL though. It's based on product type to determine whether I need to calculte the difference between DATE1 to DATE3 or TEXT3 to DATE3


Thanks again. Have a great weekend!

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931