# Thread: Excel help please

1. ## Re: Excel help please

Originally Posted by MushHead
With base year in A1:
=IF(LEN(A1)<5,A1&"/"&MOD(A1+1,100),LEFT(A1,4)+1&"/"&MOD(LEFT(A1,4)+2,100))

That just copies down a column nicely:
2022
2022/23
2023/24
...

Note: this was in LibreOffice, which seems happy treating numbers as strings & vice versa. Excel may be the same?
I did that before I saw your spreadsheet, which makes it a bit trickier to have a single formula to do the conversion from the base year, since it's not in the same place as the rest of the cells - this will mean that your 1st cell in the lower list will have a different formula than the rest - basically the 2 parts of the IF() function.

In A36: =D3&"/"&MOD(D3+1,100)
In A37 onwards: =LEFT(A36,4)+1&"/"&MOD(LEFT(A36,4)+2,100)
Brilliant! That works perfectly. All I need to do now is analyse the formulas so I can understand exactly what is going on.
Thanks very much.

2. ## Re: Excel help please

I don't have access to excel any more or I'd be able to figure it out. Something I have done in the past as a cheat when having problems is to use multiple cells with their own formulas and just format the sheet to look like it's a single cell. I also like to make the formula in small easy to understand chunks before combining it all into a longer one.

Something simple like =D2+1&"/"&D2-1998 might work for the first cell but it won't autofill and work, you'd need a 2nd formula from the next line onwards. Also I can't test that so it might be garbage.
Next line onwards might be =LEFT(A36,4)+1&"/"& RIGHT(A36,2)+1 really don't know if it'll work but maybe.

If statements are more elegant, but if I can get away with simple arithmetic I will.

It's been too long to tell you how to do my next suggestion but I recall there is a way to format cells to always use certain formatting so it might be possible to make the "/" part of the cell

3. ## Re: Excel help please

Thanks everyone for the help. MushHead gets the gold star for a solution that works.

Page 2 of 2 First 12

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•