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
1486666	2022-06-05 05:03:00	I have a value in spreadsheet cell of 2022 (Base financial year). I can make this text or number. I want to propagate this through other cells (future financial years) as: 2022/23 2023/24 2024/25 etc. I've tried various variations on LEFT,RIGHT,MID,TRUNC etc, but can't get it to work. Any suggestions from people more expert/less rusty in Excel will be very welcome. TIA	Tony (4941)
1486667	2022-06-05 08:26:00	Strange. '2022/23' in a cell works fine in Libre Office calc as text or number.	linw (53)
1486668	2022-06-05 09:15:00	Strange. '2022/23' in a cell works fine in Libre Office calc as text or number. Its not the presentation - that's fine. I need a formula that I can put in one cell and do a fill down and have it increment automagically.	Tony (4941)
1486669	2022-06-05 11:54:00	I did similar years back using test data against dates and to increase values by dragging downward. The dollar sign is used in the formula to set up absolute, relative cell reference then drag/fill down. Bit difficult to explain. See this website (www.ablebits.com ell%20reference%20affects%20just,reference%20that% 20won't%20change.). YouTube has tutorials, But might be tricky with your date format, we would convert to julian date codes.	kahawai chaser (3545)
1486670	2022-06-05 21:44:00	I think I haven't explained clearly enough what I am trying to do. The spreadsheet is a financial model of projected future income and expenses. he base cell "2022" in the example is the current financial year, and is updated manually every year. The propagated cells "2023/24" etc are just row labels for financial information for future years. There is no calculation based on them except to generate the next year's label. I could simplify everything by just making the year label "2023","2024" etc. That would be easy. Cell A13 contains "=$A12+1", etc. For my own education and entertainment I'm just trying to make it a bit more fancy.	Tony (4941)
1486671	2022-06-05 22:30:00	I think I haven't explained clearly enough what I am trying to do. . Had my son whose a wiz with excel look at the original question last night, and thats exactly what he said as well. :) What he said was if you a re able to give an actual example of what you are wanting the rest should be easy. Meaning write out an example spread sheet and manually add in a few lines of what's wanted. Then post a screen shot of the details. (picture is easier as explaining often is wrote / read wrong or confusing) He did similar for me on a spreadsheet for invoices Purchased /due Dates. You add in the invoice purchased date and it automatically displays the due date for the 20th of the following month in another Cell. Some Venders are 20th some are 2 weeks. Took me a whole day of head scratching trying to get it to work, -- NO Go. he took one look ( gave me a your joking look) said move over and tap tap tap tap- There done. :eek: He then asked if he wanted to make it so still owing is in red and paid is in Green ??? No thanks. Works damn well. Looked at the actual formula he used :confused::waughh:	wainuitech (129)
1486672	2022-06-05 23:11:00	As per Wainuitech's suggestion, here is a snip from the spreadsheet. Hope it makes things clearer. 11327	Tony (4941)
1486673	2022-06-05 23:25:00	He wont be up for a few more hours but if no one else comes up with any workable answer, ( its all double Dutch to me) :D I'll ask him. This is the setup he did for me, put in date and auto displays due date a few cells over. 11328 And here's the formula he used. =IF(A855="","",DATE(YEAR(A855),MONTH(A855)+1,20)) :confused::confused::waughh::waughh:	wainuitech (129)
1486674	2022-06-05 23:51: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)	MushHead (10626)
1486675	2022-06-05 23:55:00	He wont be up for a few more hours but if no one else comes up with any workable answer, ( its all double Dutch to me) :D I'll ask him . This is the setup he did for me, put in date and auto displays due date a few cells over . 11328 And here's the formula he used . =IF(A855="","",DATE(YEAR(A855),MONTH(A855)+1,20)) :confused::confused::waughh::waughh: Thanks WT . Of course mine aren't actually dates - they just look like them . I guess the basic algorithm is, starting in cell A36: Action Result Formula Take the base date "2022" =$D$3 Concatenate "/" "/" &"/" Concatenate last 2 characters of base date, +1 23 ????? cells A37 - Axx are then derived from A36	Tony (4941)
1 2