IsDate() formula returning "#Format"

building reports webintelligence
1

I have a date field that I am trying to determine number of as days since that day. I used DaysBetween(CurrentDate();MyDate) and then just subtracted them CurrentDate() - MyDate. I get “#Format” returned as value. So I did IsDate(MyDate) formula and it’s also returning “#Format”. I created a variable ToDate(MyDate;“MM/dd/yyyy”), then FormatDate(ToDate(MyDate;“MM/dd/yyyy”);“MM/dd/yyyy”), still getting “#Format” value returned. Ug!

Any help would be appreciated.

turbobeagle :us: (BOB member since 2008-02-29)

2

Hi,

What is MyDate? Is it a universe object or a report level variable? If the latter what is its formula? And what type is MyDate of? Can you also give some values as an example?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Hello,
“MyDate” is a universe field that is a date. I looked at the Oracle database schema behind the universe, and it is indeed formatted as a date (timestamp 26), and the universe is not modifying it in any way, so I’m not sure why BO’s currentday() field and this date field aren’t playing together.

An example: 02/17/2010
“MyDate” is always MM/dd/yyyy

Thanks

turbobeagle :us: (BOB member since 2008-02-29)

4

Hi,

To find the number of days between 2 dates, you can’t just simply deduct date one from date two:

CurrentDate() - MyDate

Instead of that, you need to use DaysBetween(date1,date2) function.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

The first step is right. What are you trying to accomplish by the second formula? Are they not the same? :?

Jansi :india: (BOB member since 2008-05-12)