Amend month number from its calendar value to a relative one.

The "Month" function can return the month from a date, but my analysis is on a sporting calendar which runs from August to May. So, I'd like all dates in August represented as month number 1, through to May (when the season ends) which would be month 10 in that time-span. Attached is a sample of the data with the relevant columns coloured.

Note that the data will span several years, too. I would like the months to be fixed as values between 1 and 10 for all years, rather than increase sequentially with each year, however. IE May in any year will be represented as 10.

Hope this makes sense. Very grateful to anyone who cares to help.

Many thanks
Rob C

Try

=LOOKUP(MONTH(B2),{1,2,3,4,5,6,7,8,9,10,11,12},{6,7,8,9,10,0,0,1,2,3,4,5,6})

That's cracked it, John.
Thanks very much indeed.
Best wishes
Rob

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.