Academic Year as Column Headings - Auto Update

[xybadog]

I need to have column headings of months of the academic year starting in August. My problem with this is, for example, the current academic year started on 1 Aug 15. When we reach the 1 Aug 16 I need the year of all months to increase by one, ad so each subsequent year.

What formula would I use?

[AliGW]

Is this on the same sheet or a different one? Have you considered selecting all of the dates and doing a quick FIND & REPLACE?

[xybadog]

On the same sheet. Each column heading is the first of each month. I just need each year in the date on that row to increase by one on 1 August each year. Rather than simply enter the date as text in each header cell I want to input a formula to take care of this.
So currently, I have 12 cells (A1:L1) each with the first day of each month from 1 Aug 15 to 1 Jul 16. I want to input a formula instead into each of these cells to display the same dates but on the 1 August this year I need the year in each to increase by one so that it’s automatically set up for the next academic year. Then next year, on 1 Aug, the same to happen for the following academic year.
Hope I’m making sense.

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[xybadog]

Umm,I'm at work and the browser security restricts me from uploading files on this forum only for some reason.
Anyway, I’ve attached a screenshot instead.
Basically I want to enter a formula in A1:L1 to show each of those dates but on 1 August every year the year in each of those cells increases by one so that it shows that current academic year.
I’m guessing I’m not explaining this too well.


Untitled.jpg

[AliGW]

Try this in A1 and drag it across:

=IF(MONTH(DATE(TEXT(NOW(),"yyyy"),COLUMN()+7,1))>7,DATE(TEXT(NOW(),"yyyy"),COLUMN()+7,1),DATE(TEXT(NOW()+1,"yyyy"),COLUMN()+7,1))

Set cell formatting to suit.

[xybadog]

Try this in A1 and drag it across:

=IF(MONTH(DATE(TEXT(NOW(),"yyyy"),COLUMN()+7,1))>7,DATE(TEXT(NOW(),"yyyy"),COLUMN()+7,1),DATE(TEXT(NOW()+1,"yyyy"),COLUMN()+7,1))

Set cell formatting to suit.

That actually returns the wrong result. It’s one year ahead because currently it should start 1 Aug 15 and once we get to 1 Aug 16 it should then show 1 Aug 16 and so on.

[AliGW]

OK, so I'll fix it!

=IF(MONTH(DATE(TEXT(NOW(),"yyyy"),COLUMN()+7,1))>7,DATE(TEXT(NOW(),"yyyy")-1,COLUMN()+7,1),DATE(TEXT(NOW(),"yyyy")-1,COLUMN()+7,1))

[xybadog]

OK, so I'll fix it!

=IF(MONTH(DATE(TEXT(NOW(),"yyyy"),COLUMN()+7,1))>7,DATE(TEXT(NOW(),"yyyy")-1,COLUMN()+7,1),DATE(TEXT(NOW(),"yyyy"),COLUMN()+7,1))

I was trying to figure it out but couldn’t. However, this one also doesn’t return the correct value.

[AliGW]

I am assuming that each sheet will only contain columns for one academic year? If not, then you need to make it clearer what you want.

[AliGW]

I was trying to figure it out but couldn’t. However, this one also doesn’t return the correct value.

I've changed it again - copy and paste again. And then, please, try to work through it and understand what it is doing so that YOU can tweak it in future. Ask if there is anything about the syntax you can't work out.

[xybadog]

I've changed it again - copy and paste again. And then, please, try to work through it and understand what it is doing so that YOU can tweak it in future. Ask if there is anything about the syntax you can't work out.

Thanks that works. I’m uncertain about the COLUMN function but will Google that later when I get some time to look into it.
Thanks for your help.

[AliGW]

OK - so, column() returns the number of the column the cell is in. So, if it is in column A, it returns 1, in B, 2, and so on. Does this help?

Thanks for the rep, by the way!

[AliGW]

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