View - Date Values
Hello Group Members,
Dear Brian,
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
Hello akwatdok,
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.
I agree that it becomes a
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
Hello, I am having
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!