This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.
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.
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".