Changing a date type

[jjackie]

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! 

[jrgo · Accepted solution]

@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]

Thanks Jimmy!