community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Convert day and year to date

I am super new to Alteryx, talking like a week and half.  I am trying to convert a date from the format YDDD to mm/dd/yyyy.  For example:

 

I have a cell that contains 8263, which is the 263rd of 2008, 9/19/2008.  I can do this in VBA and python, just cannot figure out the right formula in Alteryx.  Can anyone help me or point me in the right direction? 

 

Thanks.

Alteryx Certified Partner
Alteryx Certified Partner

How about this?

 

DateTimeAdd(
ToString(
	ToNumber(
		left(ToString([Field1]), 1)
		)+2000
	)+
"-01-01",toNumber(substring(Tostring([Field1]),1))-1
,"days")

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Bolide

@oneillp111  One other approach would be in a formula tool use:

 

datetimeparse((tostring(2000+tonumber(left([Field],Length([Field])-3)))+right([Field],3)),"%y%j")

Alteryx recognizes julian dates, which is what you're working with here.


That said, this is the standard date format which is YYYY-DD-MM, and not the format you specified below. Wrap the above in a

 

datetimeformat([above formula],"%m/%d/%Y")

 

and you would get your desired output.

Quasar

Welcome @oneillp111 

I took a similar approach to @MarqueeCrew , just broke it down step by step.

image.pngimage.pngimage.png

Cheers!
Esther

Labels