Replicating an Annual Schedule Worksheet

Jem

I am trying to replicate annual pre planned maintenance schedule worksheets eg. copying 2018 and creating a 2019 worksheet. I have done so by opening my 2018 worksheet and 'saving as new' renaming to 2019 then deleting all the 2018 data. I just noticed the conditional formatting based on dates is still set to 2018 so I need to manually change these many times.

Does anyone know of an easier way of replicating these annual schedules?

Many thanks in advance!

Paul Newcome

Can you provide details/screenshots of exactly how the conditional formatting works?

Jem

Hi Paul,

 

Thanks for your reply, I have attached a screenshot. The conditional formatting works as parameters to ensure a pre planned maintenance job is completed within the given timeframe. Is there a quicker way of pushing all of theses dates forward a year, rather than doing them manually?

 

Kind Regards

 

Andrée Starå

Hi Jem,

Unfortunately, there isn't.

If it's a lot of work to update the Conditional Formatting, I would recommend creating a structure that checks for the same information but on the sheet instead and use that for the Conditional Formatting.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I agree with Andree. I use a lot of helper columns (mostly checkboxes) for my conditional formatting, and I label the columns to reflect exactly what the issue is. For example I'd have a [Late Start] and a [Late Finish] checkbox column with a formula to check the box if the start or the finish is late accordingly. I'll then base my conditional formatting off of those columns to say "If Late Start is checked, apply this format".

 

If I am using the same sheet layout across multiple years, I'll also use a visible cell somewhere to enter the corresponding year and then reference that cell in my DATE functions. That way you can "Save as new", change the year in that one cell from 2018 to 2019, and update all of your DATE functions simultaneously.

 

For example... In "[Primary Column]1" I would enter 2018. In my [Jan Bad] column, I would use something along the lines of 

 

=IF(Jan@row > DATE($[Primary Column]$1, 2, 1) - 1, 1)

 

This will check the box if Jan is greater than the day before the first of Feb. Using this allows you to use the same formula (with the exception of Dec which is an easy fix) for all months regardless of 28, 29, 30, or 31 days in the month (gotta love the leap year).

 

You can then setup your conditional formatting to say that "If [Jan Bad] is checked, apply this format".

 

Then when you "Save as new" for 2019, you can just change the year in "[Primary Column]1" to 2019. Now your formulas will all have the correct year, and your conditional formatting will still be accurate.