Excel Calendar-how to change it to next year

I am using Excel 2010 but I am a total novice. I have never used formulas, only used Excel as a table. I have created a Calendar of Events and for the first time I discovered that I did not need to type in the dates but set a start date and end date and wow.....2016 appears in full.

Obviously I am a total novice!

My question is that most events are at the same time every year so...

What formula do I use to change the spreadsheet to 2017? I need to know where to put the formula and what it is.

Thanks

2016 copy.xls

I have attached the workbook. I am only trying to change the calendar from 2016 to 2017

Thanks

there are repetitions in the dates. What is the basis for repetitions.

Originally Posted by kvsrinivasamurthy

there are repetitions in the dates. What is the basis for repetitions.

I have attached a Master Calendar. Clubs customise the calendar for their members. For example an obedience club will find and sort all obedience competitions. If the date is not repeated they will then not have date of the competition.

Trust this makes sense. My novice status is clear. I am happy to change the way I do this if it makes better sense.

Thanks in advance.

Anyone have any ideas? Could I just add a number of days somehow? What if it is a leap year?

It is possible to put formulas. Only restriction is data cannot be sorted.
Copy the whole data and paste it other sheet by
Paste Special--> values.
Then data can be sorted in that sheet. Is it ok.

Originally Posted by kvsrinivasamurthy

It is possible to put formulas. Only restriction is data cannot be sorted.
Copy the whole data and paste it other sheet by
Paste Special--> values.
Then data can be sorted in that sheet. Is it ok.

Thanks for your suggestion.

What I need to do is to add one day to the date so the day/date is correct for 2017.

Add 366 to 2016 dates.

If your new (2017) is a copy of 2016, then in the date field you could put

='2016 Calendar - Table 1 - Table'!D8+366

The dates are the "same" but the days are not so I am not sure it is simply changing the year.

See attached.

Originally Posted by JohnTopley

Add 366 to 2016 dates.

If your new (2017) is a copy of 2016, then in the date field you could put

='2016 Calendar - Table 1 - Table'!D8+366


The dates are the "same" but the days are not so I am not sure it is simply changing the year.

See attached.

Oh dear. Then perhaps I need a column for "day" Thanks for the input.

I am thinking that an Excel tutorial might be a great idea. I had thought Excel was for accountants but this opens a huge number of ideas

As I suggested, it is bit more complex than you envisaged but people on this forum are very willing to help so please ask if you need further assistance: we were all beginners once!

I am still trying to work this out so I have simplified the calendar a bit. Can I add a formula that changes the dates to 2017 but keeps the days the same?Trial 2017.xls

With the macro it can be done. Are you ok with that.

Originally Posted by kvsrinivasamurthy

With the macro it can be done. Are you ok with that.

I am sorry to be such a nascence but I have no idea what a macro is.

I offered to help so did the 2016 one using excel as a table but didn't know when I did it that they wanted to use it as a template.

See the attached:

By adding 364 to 2016 dates you get the same day of the week for 2017 as 2016 (nearest date to 2016 date).

Originally Posted by JohnTopley

See the attached:

By adding 364 to 2016 dates you get the same day of the week for 2017 as 2016 (nearest date to 2016 date).

You are brilliant! So how do I add this? What is the formula and where do I put formulas? (I have never used formulas). Will this work on the original file I posted?

Thanks heaps

See attached:

Formula is in column D of "2017" sheet.

Please note it is not "foolproof" as an event scheduled for New Year's Day (2017) has a date of 30/12/2016 when 364 is added to 01/01/2016. You need to add 366 to get 01/01/2017.

So you will to check your dates carefully as some events (like NYD) are date-dependent not day-dependent.

Trial 2017.xls

Sue, I did mention above how to change the year but keep the same day/month quite easily without a macro.

Use the "Find & Replace" option- just highlight all of the date cells and replace 2016 with 2017 click replace all. It takes less than 10 seconds - Job, Jobbed

THANK YOU SO MUCH! You were all so patient.

Now to start an excel tutorial.