Create one date from multiple columns

[aberry0613]

Hello everyone!  I have been using Alteryx for a few days now and I am having an issue with the following.  I have data in the format below:

Fiscal Year	Period
FY17	Jan
FY17	Feb
FY17	Mar

I would like to make a column that is an actual date, something like 02-01-2017 for February.  How would I go about this?

Thank you!

[Joe_Mako · Accepted solution]

How about a formula like:

DateTimeParse([Fiscal Year]+[Period],"FY%y%b")

Be sure to set the data type to Date. for a list of Specifiers (codes for parsing and formatting), see the bottom of https://help.alteryx.com/11.0/index.htm#Reference/DateTimeFunctions.htm

[SeanAdams · Accepted solution]

Hey @aberry0613,

Welcome both to the community and to Alteryx!   The first few days can be a little confusing so very glad that you reached out.

@Joe_Mako is right about the formula, and his solution is the most efficient - but depending on when your fiscal years start and end you may end up with the wrong year (e.g. some EU entities have an FY that starts after August Break)

For example - if your fiscal year is from 01 Feb to 01 Feb, then the first row in your data (FY17: Jan) is actually 01 Jan 2018, right?

So - taking this step by step - there are quicker ways to do this, but given that you're new I thought that it would help to explode this out.

- First strip the FY off the fiscal year to get you to a number using a formula tool.   You can do this by using right([Fiscal Year],2)

- then add 20 to it, so you have 2017.   this is also just a formula (you can use the same formula tool as the one above, just add another formula below.   Formula would be setting [Fiscal Year] column, and would say '20'+[Fiscal Year]

Now you have 2017 in the FY column and Period says Jan

- then add a column called Day.   Not strictly necessary but will help you see what's going on.   Use the formula tool - set a new column called Day to have a value of 01.

Now you have 3 columns: 2017; Jan; 01

Now all you need to do is figure out which year this is.  That's relatively easy to do with a quick table, or a formula.   The thinking is that if your fiscal year starts in March, then Jan & Feb are in the next fiscal year.

So - if we created a table that had all the months, and the ones that push into next year have a flag - then we can update this..

Now we have correct year; month & day - all you need to do is use a formula to tool to get a date.

@aberry0613 - this is a long-form way of doing it, there are a lot of tricks to get round this quicker, but I figure that since you mentioned you were new, it was worth doing the long form so that you can see it, and as you become more comfortable you can compact this.

If this or @Joe_Mako's reply get you to the solution you need - would you mind marking one (or both if applicable)?  If you still have challenges or questions - feel free to reply back on this thread with questions and updates and some data, and we'd be glad to help.

Cheers

Sean

[Joe_Mako · Accepted solution]

Here is a formula for converting a FY Date into a normal Calendar date:

IF DateTimeFormat([Date],"%m-%d")>="10-01" THEN
DateTimeAdd([Date],-1,"year")
ELSE [Date] ENDIF

where "10-01" is the month-day of the FY start. In this case, FY17 Oct will become 2016-10-01

You can use it in the same Formula tool:

[aberry0613]

You all rock!  Thank you