Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Changing a date type

jjackie
5 - Atom

Hi all! 

 

I was wondering if it was possible to change a date field to a fraction of how many days it's been in the year or even getting the day number of that year. For example, changing 2/1/17 to 32/365 or just 32.

I currently have a column with rows of start dates and a column with yearly revenue. I want to use it in a formula to figure out revenue for the year for cases where the start date isn't 1/1, but instead something like 4/3.

 

Thanks! 

2 REPLIES 2
jrgo
14 - Magnetar

@jjackie

 

You’d need to make sure that your date field is properly attributed as a DATE field type, but the following expressions shound create your num and denom... also going to assume your date field is named “mydate”:

 

Num

TONUMBER(DATETIMEFORMAT([mydate],’%j’))

 

Demon

TONUMBER(DATETIMEFORMAT(DATETIMEFORMAT([mydate],’%Y’)+’-12-31’,’%j’))

 

Plug these into a formula tool and you can put it all in one new field, or put them in separate new fields and then do you fraction calc.

 

Hope this helps!

 

Jimmy

jjackie
5 - Atom

Thanks Jimmy! 

 

 

Labels