Tile applied to date

[Coriel-11]

Is there a simple way to do this. I'm trying to use the tile function to group by financial year.

So Col A has the date of sale (in DateTime format).

I'm wanting to use "Tile" or something similar so I can just put in the last day of each financial year and it will put those into bins e.g.

Sale Date

Start DateTime	F.Yr
31-03-2021 00:00	20/21
30-03-2020 00:00	19/20
03-10-2018 00:00	18/19
01-08-2017 00:00	17/18
04-04-2017 00:00	16/17

where the last day of the financial year is 31st July

It feels like a job for tile, but is there a simple way to do it without having to use lots of steps (negating the use of the tile tool)?

Thanks,

Matt

[Ben_H · Accepted solution]

Hi @Coriel-11,

It's not particularly clever but I'd just use generate rows to create a full list of all dates each financial year and join on to that.

Regards,

Ben

[echuong1 · Accepted solution]

Another alternative is to use a Formula tool.

I first converted the values to proper date format. I then used a conditional statement to check to see if the date was before or after 7/31. Depending on the date, I either added or subtracted a year.

[Coriel-11]

Thank you both for your help with this. Drawing on your ideas I did it a slightly different way in the end and made it into a macros so I can use it again.

On reflection I'm not sure it's as neat as your solutions, but it didn't seem that way when I started and I learnt a bunch of stuff doing it so thank you.