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!
