| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 150713 | 2022-06-05 05:03:00 | Excel help please | Tony (4941) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1486676 | 2022-06-06 00:15:00 | 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. |
Tony (4941) | ||
| 1486677 | 2022-06-06 07:27:00 | 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 |
dugimodo (138) | ||
| 1486678 | 2022-06-06 09:32:00 | Thanks everyone for the help. MushHead gets the gold star for a solution that works. | Tony (4941) | ||
| 1 2 | |||||