View - Date Values

Brian Element

Forums

Hello Group Members,

 
When you import any data file into IDEA having Date Fields you will notice by default that the Fields get displayed in IDEA as per the Regional Date Settings of the PC on which IDEA is installed.
 
So a sample Date 14-FEB-2019 from any Report will get displayed in IDEA as 14-02-2019 if the Regional Setting of Dates on the PC is DD-MM-YYYY
 
If you need to change the Display View of the Date, simply select the Date field within the active IDEA database, then navigate to View and click on Date Values under Format.
 
Now under Date Values you can choose the preferred Date Display format of your choice like YYYY/MM/DD or Month Date, Year.
 
This will only change the Display Date View. However the Date will continue to be stored in IDEA as YYYYMMDD.
 
So the Display Date will not affect the way we build Equations/Expressions/Criteria in IDEA.
 
So an Analytical Requirement to identify lapsed Agreements as on Date will be written in the Equation Editor as ~ Agreement Expiry Date < "20190214"
 
Best Regards
 
Group Admin Team

MJ Sat, 04/13/2019 - 21:49

Hello, I am having difficulties with a date question. I need to append the day of the week my purchases were paid from the field (PAY_DATE) in a virtual column then called DATE_PAID. Then I need summarize total purchases paid and average for each of the 7 days of the week—Sunday (Day 1) through Saturday (Day 7). I do not know how to get the dates (currently in the format of YYYYMMDD) to days of the week (1-7).
Thanks!

akwatdok Wed, 12/23/2020 - 08:18

Dear Brian,
I have read many posts here relating to dealing with date formats. However, i see that non of the answers addresses my concern of dates exported from Excel into IDEA in the US and UK formats but in the same field. so we have dates like 22/10/2019 (UK) and 10/22/2019(US). There is also 5/10/2019 and 10/02/2020. But in reality all these dates were for the month of October. They are already imported in IDEA in that way and in the field is character. I want to change these to one date mask in date field. i tried but failed.
David
 
 

Brian Element Sun, 12/27/2020 - 05:52

Hello akwatdok,

So you have a mix of US and UK time formats in the same field?  That becomes really difficult for dates where the day is 12 or less because it becomes impossible to tell if it is US or UK just from looking at it.  For dates with days larger than 12 you can use some logic looking for anything larger than 12 and then assume that is the day and the other part is the month.  Is there anything else in the file which would indicate if the date relates to US or UK?  If not then it becomes a guessing game what it should be.

akwatdok Fri, 09/02/2022 - 10:18

I agree that it becomes a guess work. But where the date is beyond 12 it is possible to know, and following up from your script below which i think is very useful, i want to apply it in this scenario in my table but when i reach 12/31/2019 i get stuck. @CompIf (@Mid(DATE, 2, 1) = "/"  .AND. @Mid(DATE, 4, 1) = "/", @Ctod(DATE, "D/M/YYYY"),@Mid (DATE, 2, 1) = "/”. AND. @Mid (DATE, 5, 1) = "/", @Ctod (DATE, "D/MM/YYYY"), @Mid (DATE, 3, 1) = "/”. AND. @Mid (DATE, 5, 1) = "/", @Ctod (DATE, "DD/M/YYYY"),1,      @Ctod (DATE, "DD/MM/YYYY")).

DATE (Character)

1/2/2019

1/02/2019

01/2/2019

12/12/2019

12/31/2019