system
December 21, 2011, 3:52pm
1
Hi All,
I am trying to find the difference between two date in XIR3.
[Loss Date] [Date Reported to Employer]
For this am doing, =DaysBetween([Loss Date];[Date Reported To Employer])
Getting an error message, The expression or sub-expression at position 13 in the ‘daysbetween’ function uses an invalid data type. (WIS 10037)
can any one help me out on this. Its urgent.
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 3:54pm
2
Welcome to B:bob:B !
Have you checked both those dates? I bet one of them is stored as a character string. If it is you will need to convert it to a date. As an aside, this is a volunteer board, so it is considered rude to say ‘this is urgent’.
Nick Daniels (BOB member since 2002-08-15)
system
December 21, 2011, 4:54pm
3
yes. all of our dates are stored as alpha in format “YYYYMMDDHHMMSS”
So, how do i find the difference between those two. Am new to business objects
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 5:02pm
4
Try
=DaysBetween(ToDate([Loss Date];"yyyyMMddhhmmss");ToDate([Date Reported To Employer];"yyyyMMddhhmmss"))
sredden1 (BOB member since 2011-06-09)
system
December 21, 2011, 5:13pm
5
Thank you, its working
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 5:16pm
6
=DaysBetween(ToDate([Loss Date];“yyyyMMddhhmmss”);ToDate([Date Reported To Employer];“yyyyMMddhhmmss”))
When i use this in the report the data is not comming, the data what it is giving is #ERROR
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 5:23pm
7
Can you go into universe designer and check the object type (i.e., see whether it’s showing as a date field, or a string, etc.)…? You may need to convert them to dates from there.
sredden1 (BOB member since 2011-06-09)
system
December 21, 2011, 5:34pm
8
We don’t have an access to the universe. I’ll check it with my universe developer. Thank you
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 5:44pm
9
if i bring the [Loss Date], [Date Reported to Employer] fields to the report individually am getting the data as 02/15/2000 , 01/13/1999, …
but if i use this as a variable
=DaysBetween(ToDate([Loss Date];“yyyyMMddhhmmss”);ToDate([Date Reported To Employer];“yyyyMMddhhmmss”))
The Data is #ERROR .
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 5:53pm
10
if i bring the [Loss Date], [Date Reported to Employer] fields to the report individually am getting the data as 02/15/2000 , 01/13/1999, …
but if i use this as a variable
=DaysBetween(ToDate([Loss Date];“yyyyMMddhhmmss”);ToDate([Date Reported To Employer];“yyyyMMddhhmmss”))
The Data is #ERROR .
Okay, one more go at this… Create a variable for each of the fields using the ToDate function (I’d suggest formatting them as MM/dd/yyyy, since that’s what they seem to be showing as), then put the variables in the DaysBetween formula. It seems to work for me.
So, to be clear - for [Loss Date] create a variable using
ToDate([Loss Date];"MM/dd/yyyy")
Then do the same for [Date Reported to Employer]
ToDate([Date Reported to Employer];"MM/dd/yyyy")
Finally,
DaysBetween([variable1];[variable2])
sredden1 (BOB member since 2011-06-09)
system
December 21, 2011, 6:03pm
11
I tried in your way. The formula is working but the data is not getting. The data is displaying #ERROR
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 6:10pm
12
i gave a try like this also, =FormatDate(ToDate([Loss Date];“MM/dd/YYYY”);“dd/MM/YYYY”)
Still getting data as #ERROR
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 6:13pm
13
Ah, I think I see the problem… the ‘YYYY’ needs to be ‘yyyy’… The date formatting won’t recognize it when it’s capitalized.
sredden1 (BOB member since 2011-06-09)
system
December 21, 2011, 6:13pm
14
My guess is that you are using a wrong data format in ToDate() function? Can you post the sample dates?
Jansi (BOB member since 2008-05-12)
system
December 21, 2011, 6:19pm
15
Yes there you go, it is not recognizing “YYYY”. now its getting
Thank you…
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 6:22pm
16
Total no.of claims selected for a specific selected location where Loss date vs. reported date difference is in the 0-3 days range
for this. how should i write a variable
clpa (BOB member since 2011-08-18)
system
December 21, 2011, 6:23pm
17
This can be a spearate thread as the problems are totally different. Also while you create a new topic, please also post the code that you’re having in your mind.
Jansi (BOB member since 2008-05-12)