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

Need Help Converting an Excel Formula into Alteryx

hstgeorge
5 - Atom

I am looking to convert the below formula from excel into alteryx:

 

=TEXT(DATE(YEAR(Z2),MATCH(Z2-DATE(YEAR(Z2),1,1),{0,29,57,92,120,148,183,211,239,274,302,330}),1),"mmmm yyyy")

7 REPLIES 7
neilgallen
12 - Quasar

taking a bit of a shot in the dark here, but it looks like you're trying to convert a date value into a "Month Year" format.

 

Assuming you have your dates in a field, you can use a formula tool to create the formatted date as

datetimeformat([field],"%B %Y")

 

where %B is the full month name, and %Y is the four digit year. 

hstgeorge
5 - Atom

So, it is taking a date such as 2/11/2020 and converting it on a couple items.

 

It does produce an end result that is "February 2020"

 

but the remainder of the formula is also placing the date in the correct month based on a 445 retail/fiscal calendar - for instance some dates in november are actually in december and some dates in january are actually in february and the trend continues throughout the year.  I am using this formula in excel but can't figure out how to translate it.

 

Maybe there's a better way to group the dates in the correct months on a 445 calendar.

tonypreece
10 - Fireball

I've got a solution for you, but I wonder if anyone has a more elegant way of doing it?

 

This workflow assumes your financial year begins on 1 January.

 

tonyp_0-1581613562216.png

The initial formula works out the 'first day' of each month according to the rule of 445, then it's a matter of transforming it into two columns (month name and start date) before joining it with your dataset and the date that you want to test.

After the join the formula works out which month your date falls into and outputs that as the MMMM YYYY string that you need.

tonyp_1-1581613773524.png

neilgallen
12 - Quasar

if you're concerned with the fiscal calendar part, I'd reference this post for an example on how to create.

hstgeorge
5 - Atom

I am not sure I understand where to join my data to this.  It works brilliantly.  My data set has around 40 columns and one of those is delivery date.  I want to convert the delivery date to the month year as you have done in the workflow attached.

tonypreece
10 - Fireball

Hi @hstgeorge 

 

You should remove the input tool below the Append Fields tool in the middle and feed your data into that Append Fields 's' input anchor.

 

In the two formula tools to the right of Append Fields, change [CheckDate] to match the name of your delivery date field (e.g. [Delivery Date]).

One other thing, the Append Fields defaults to error if you're working with more than 16 rows of data. If your data has more than 16 records click on that tool and at the bottom of the configuration pane change "Error on appends of more than 16 records" to "Allow all appends". 

hstgeorge
5 - Atom

That was rather easy!!!  Thank you for your assistance @tonyp!!!  I am sure I will have more questions down the road, but for now this absolutely solves my problem!!!!

Labels