Count Days to Current Date

[WPCMS]

I am trying to figure out how to take a date field and count how many days to current date.

Ex: {DateReceived} to currentdate
returns 7

// {07/10/7} to 07/17/07
//returns 7

Thanks,

WPCMS

 

[TheBlondOne]

You can create a formula and type today - {DateReceived}

or

datediff("d",{DateReceived},today)

HTH

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[WPCMS]

Steve

I tried both formulas and it says {DateReceived} needs to be a date not a field. I am using Crystal 9, would that affect formulas?

 

[HowardHammerman]

The version is not relevant.

What Steve was suggesting is that you need to include the field in you formula. For example:

DateDiff ("d",{Orders.Order Date} ,CurrentDate )

Where {orders.order date} is a field in the Orders table.

CurrentDate is a function.

I hope that helps.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[WPCMS]

I had abreviated the field, maybe which I shouldn't have done. But below is my version of what is above. Still CR says {Asgnmnt.CS1stDateRetRcptRvcd} requires a date.It is a date field not a date.

DateDiff ("d",{Asgnmnt.CS1stDateRetRcptRvcd} ,CurrentDate )

What am I still doing wrong?

 

[HowardHammerman]

The field in question must not be of type date or date/time.
Perhaps it is a character field holding the date?

Include an exmple of some of the raw data from the field.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[WPCMS]

I am extracting from FrontRange HEAT program and it is a date field. There are hundreds of dates in this form:
2003-10-01
Could that be the problem? Or is there something wrong with my Crystal Reports?

 

[HowardHammerman]

You have isolated the problem. It really is a character field holding the date. Create a formula with the following expression:
-------------------
numbervar yr := tonumber({table.field}[1 to 4]);
numbervar mo := tonumber({table.field}[6 to 7]);
numbervar dy := tonumber({table.field}[9 to 10]);
date(yr,mo,dy)
-----------------------

Use that field in your calculations.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[WPCMS]

It works! Thank you! My report is complete. There is one thing though, whenever I preview it takes me back to the formula and says tonumber ({Asgnmnt.CS1stDateRetRcptRvcd}[1 to 4]) the string is non numeric. I click ok and view the report. Should I just ignore this alert?

WPCMS