Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13
  1. #11
    Senior Moment Tony's Avatar
    Join Date
    Dec 2004
    Location
    Waitakere City
    Posts
    4,675

    Default Re: Excel help please

    Quote Originally Posted by MushHead View Post
    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.
    We are all but temporary files on the great flash drive of life.

  2. #12
    amateur expert dugimodo's Avatar
    Join Date
    Dec 2005
    Posts
    8,245

    Default 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
    Ryzen 2700X, 16Gb DDR4RAM, 512GB M.2 NVME SSD, MSI GTX1070

  3. #13
    Senior Moment Tony's Avatar
    Join Date
    Dec 2004
    Location
    Waitakere City
    Posts
    4,675

    Default Re: Excel help please

    Thanks everyone for the help. MushHead gets the gold star for a solution that works.
    We are all but temporary files on the great flash drive of life.

Similar Threads

  1. Excel Documents - Opening in New Excel Window
    By learning in forum PressF1
    Replies: 4
    Last Post: 16-11-2012, 02:50 PM
  2. Excel message on opening up excel.
    By Les Radley in forum PressF1
    Replies: 3
    Last Post: 29-03-2010, 01:49 PM
  3. Replies: 1
    Last Post: 28-09-2008, 11:35 PM
  4. Excel 97 to Excel 2000 Files
    By Scott Vail in forum PressF1
    Replies: 1
    Last Post: 03-07-2003, 03:26 PM
  5. Excel 2000 & Excel 97 toolbars
    By in forum PressF1
    Replies: 0
    Last Post: 11-06-1999, 08:27 AM

Posting Permissions

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