Working with date, weekday and weekend

19 posts / 0 new
Last post
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi William,

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.

Brian

William Yong
Offline
Joined: 06/30/2014 - 00:37

Hi Brian,
My Issue_Date is date field. Please refer to the attached error screen grab. Thanks.
Regards,
William
 
 

Images: 
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

William it returns a numeric and not a character.  Try changing the field type to numeric.

E.IDEA
Offline
Joined: 07/24/2016 - 15:04

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?
 
 

Images: 
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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.

Brian

E.IDEA
Offline
Joined: 07/24/2016 - 15:04

Thanks for reply! It really works! :D
 

Sedem's picture
Sedem
Offline
Joined: 07/19/2018 - 03:32

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.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hello Sedem,

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.

Brian

Pages