Financial year day,week,month columns

[ashwin_s]

Hello,

Please find the attached Excel workbook displaying the desired output.

I'm looking to create automated columns from formulae on a date field [Date] that buckets them into day, week & month for the current year ([Day], [Week], [Month]) and previous financial year ([LY Day], [LY Month], [LY Year]) starting on the 1st of August of each year, with all cells being zero if they sit outside these windows.

There's also the nuance of the week number being > 0 even if a particular date from [Date] sits outside the financial year, if the first date of the financial year (ie. Aug-01) is in the same week but not on Monday. So for instance for the financial year starting Aug-01 2018, which is a Wednesday, we should have the week number for Jul-30 & Jul-31 be equal to 1, even though they sit outside the financial year.

This process should obviously be robust for leap years.

Would appreciate any help, thanks!

[RolandSchubert]

Interesting problem, the attached workflow should be a first solution.
Just enter the required Fiscal Year in the Text Input tool and try ...

[ashwin_s]

Hi there, can't see any attached workflow?

[RolandSchubert]

Sorry, this time with attachement.

[ashwin_s]

Thank you so much for this!

Just seems to be an issue with the LY Week column where the count starts a few days too early (26/07/17).

I've attached an Excel workbook (desired output left, alteryx output right) which highlights the discrepancies to make it super clear, any suggestions on how to amend this? I'm fairly new to multi-row formulae so can't quite spot how to robustly correct the issue in the icon.

[RolandSchubert · Accepted solution]

This one should work ... typo within the multi row formula corected
(copy/paste issue).

[ashwin_s]

Amazing, thanks so much! I've used the workflow to output a mapping table and joined the output into other relevant workflows, if anyone's interested. It's not as straightforward to paste it into existing workflows.