Hoping for some guidance in using RPA to convert a Julian date (yyyyddd) that is stored as text into a usable date format I can use later to compare against. I am using an API to retrieve data from our core system with RPA and this Julian date is stored with other text. For example, it will bring back 2020093, and I would like to convert it to 4/02/2020. Has anyone figured out how to accomplish this with formulas?
Any suggestions would be greatly appreciated.
Best answer by john_armstrongView original
I would use the "DateAdd' formula for this. First, you'd need to seperat the year and the day using Left and Right formulas. Then in the DateAdd, you can add the days to 12/31/[previous year].
Let me know if you'd like more details or if this is enough to get you going.
I had to add a join into the Date section of DateAdd formula in order to get the 12/31/ to combine with the subtract and left formulas. I've attached a picture for reference if anyone is interested.