I just tried it and it worked for me. Is your Issue_Date a date field or character? It should be date. Can you give me a screen grab of the error and your equation?
When you get the custom function working then you would just summarize on the week of the year field and extract anything over 3.
My Issue_Date is date field. Please refer to the attached error screen grab. Thanks.
William it returns a numeric and not a character. Try changing the field type to numeric.
Hi, i have the following data in at the same column. Which equation can i use to convert the data at the same date format in IDEA?
Hi and welcome to the site.
One of the big problems is date formats. One way to get around this problem is to create a virtual field to hold the date and use the following equation which looks at the four different possibilities.
@CompIf(@Mid(TRANSACTION_DATE, 3, 1) = "/" .AND. @Mid(TRANSACTION_DATE,6, 1) = "/", @Ctod(TRANSACTION_DATE, "DD/MM/YYYY"), @Mid(TRANSACTION_DATE, 3, 1) = "/" .AND. @Mid(TRANSACTION_DATE,5, 1) = "/", @Ctod(TRANSACTION_DATE, "DD/M/YYYY"), @Mid(TRANSACTION_DATE, 2, 1) = "/" .AND. @Mid(TRANSACTION_DATE,5, 1) = "/", @Ctod(TRANSACTION_DATE, "D/MM/YYYY"), @Mid(TRANSACTION_DATE, 2, 1) = "/" .AND. @Mid(TRANSACTION_DATE,4, 1) = "/", @Ctod(TRANSACTION_DATE, "D/M/YYYY"))
Hope this help.
Thanks for reply! It really works! :D
Hi Brian, I am very new in ideascripting. Apparently I have a date column which represent date of birth and I want to find exact date after 60years from this date of birth.
I came up with this equation for the equation editor that will add 60 years to the date of birth field. This equation assumes that the date of birth is already a date field. For this to work create a virtual date field and use the following formula:
@Ctod(@Replace(@str(@year(DOB) + 60, 4, 0) + @str(@Month(DOB), 2, 0) + @str(@day(dob), 2, 0), " ", "0"), "YYYYMMDD")
Just replace the DOB for the name of your field. What it does it takes the date of birth, strips it down to year, month and day (all of these are in numeric format), adds 60 to the year and then changes the numbers to strings, puts them back together again and then changes the characters to a date.
Hopefully this makes sense.
IDEA is a trademark of CaseWare International Inc., used by permission.
All material provided on the IDEAScripting website is presented as is without any warranty or guarantee. Anybody using any solution provided on this pages is encouraged to validate the effectiveness and reliability on their own.
IDEA Help Videos
IDEA Related Videos
IDEA Success Stories