date difference between constant and a database field date

building reports webintelligence
1

Hello All,

I have to find difference between due date of an invoice and a fixed date. Due date is a database field and fixed date can be anything. I created the following variable:

Refdate
=DaysBetween(ToDate([Due Date];“MM/DD/YYYY”);ToDate(‘1/12/2011’;“MM/DD/YYYY”))

The formula validates fine but doesn’t give results and shows an error. What am I doing wrong. Also, is there a way to replace the fixed date (‘1/12/2011’) with a prompt?

Thanks a lot!

Purnima Sharma

prnmsharma :us: (BOB member since 2008-03-18)

2

try this (case sensitivity with the format)

basically lower case dd and lower case yyyy

=DaysBetween(ToDate([Due Date];“MM/dd/yyyy”);ToDate(‘1/12/2011’;“MM/dd/yyyy”))

Naresh Ganatra :us: (BOB member since 2004-04-09)

3

Since Due date is already Date data type. You can try this

=DaysBetween([Due Date];ToDate("1/12/2011";"MM/dd/yyyy"))

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

4

Thanks a lot! It works perfectly. Is there any way to replace it with a prompt?

prnmsharma :us: (BOB member since 2008-03-18)

5

Use UserResponse(): Something like this:
FormatDate(ToDate(UserResponse(“Enter Date”);“M/d/yyyy”);“MM/dd/yyyy”)

replace “Enter Date” with your prompt text.

Here Input date format is as per you are providing to the prompt.

SP1584 (BOB member since 2011-01-07)