Greetings,
What are the best practices for converting a Julian format date into a regtular dates? From this YYYYDDD to this AAAAMMDDIf possible, I would like to get an answer that could be easily applied by a not very tech savvy user I am supporting.
Images:
Files:
Hi Winser,
You can use the following equation editor formula for this one:
@ctod(LINE_EXPIRY_DATE, "YYYYDDD")
Since your date is in a character format you can use the @ctod() function to change it. You just give the field name and the date format which in this instance is YYYYDDD. Also the virtual field you create to hold the result should be a date field.
Hope that helps you out.
Thanks for taking the time to reply.
I am sad to inform you that this formula did not work for me, but I might be doing something wrong.
I tried another approach that is almost giving the right answer:
@Jtod((@Val(@Left(LINE_EXPIRY_DATE, 4)) * 365) + @Val(@Right(LINE_EXPIRY_DATE, 3)))
Unfortunately, my latest approach is a little bit off. I tried comparing my results to online JD to Gregorian Calendar converters and just not getting the right answer.
Your approach, on the other hand, gave me the right month and day, however, it did not give me the right year.
You will find a picture below with both results. I am at the end of my wits here, so any ideas that might help solve this issue will be greatly appreciated.
Hi Winser,
Can you give me what you think the results should be? For me the Line Expiry Date is in the modern julian date format (https://drawingwithnumbers.artisart.org/tag/yyyyddd/) where the first four digits are the year and the last 3 digits are the day number within the year. So 2000001 would be Jan 1, 2000 or 2000-01-01. The CTOD column is using the formula I mentioned above (@ctod(LINE_EXPIRY_DATE, "YYYYDDD")). An example would be line 2 or 2024052. 2024 would be the year and 052 would be the number of days. January has 31 days so 52-31 = 21 so the date would be February 31 or 2024-02-21.
Let me know if there is something that I am not understanding corretly bout your date format.
Thanks
Dear Mr. Element,
I truly appreciate you taking the time to assist me with this. Your input has been extremely valuable and has deepened my understanding of the issue at hand. However, I am beginning to suspect that the issue might actually be on my end.
As you can see from the attached screenshot, I am using the exact same formula and data source as you recommended. Despite this, I am unable to replicate your results using IDEA version 10. Additionally, a senior IDEA developer at my company tested your formula on his machine running IDEA 12, and he also encountered the same problem (please see the attached screenshot).
Could there possibly be an IDEA configuration issue on our end causing these discrepancies?PS: I have also attached the .IMD I have been working on.
Hi Winser,
Let me talk to some people and get back to you on this one as it is a strange one.
Hi Winser,
We found what the problem is. It is because the formula I gave you was for the English version of IDEA but you are using the Spanish version of IDEA so the mask has to translated to Spanish. Instead of YYYYDDD it should be AAAADDD, that is why the year was defaulting to 1900 as the equation editor didn't understand the mask.
That's fantastic news!
It's remarkable how sometimes the simplest solutions can resolve seemingly complex issues. Considering the customer's original problem, it does make sense that switching from the English version of IDEA to the Spanish version would lead to some changes, especially in terms of data masking.
Infinite thanks to you, Mr. Element. I hope you have an amazing rest of your day!