removing a date from a series of data values (and not breaking the code)

[theirlaw]

I teach English at a university in South Korea. Its been a while since I studied computers. As part of my preparations for the upcoming semester, I am making an excel spreadsheet:



https://drive.google.com/file/d/0B-Z...w?pref=2&pli=1



During this semester, there will be two public holidays (highlighted yellow using conditional formatting). I have calculated my syllabus using a formula based upon when the first day of semester for each class is respectively.



How can I remove these two days (to basically shift each column 'up') without breaking the code for days following the public holiday?

[MarvinP]

Hi theirlaw,

Try this:

In B6 put this formula and pull it down
=IF(B4+7=$I$40,B4+14,B4+7)

In G6 put this formula and pull it down
=IF(G4+7=$I$39,G4+14,G4+7)

I don't know if you want to add 14 days to those skipped days but see if this is close to what you need.

[theirlaw]

Thanks for that but it didn't seem to work...

Let me provide another example:

If I delete the value in A31 (June 6, Foundation Day), I don't want A32:35 to break. Ideally what would happen is, I can delete June 6 (and May 5), and then anything underneath that will move up by one row, while still displaying the correct dates for that class.

[MarvinP]

Hi,

I thought you'd skip ahead 14 days if you hit one of those vacation dates. I think you are better off to simply do a manual date input for the ranges below those vacation dates. I don't really know what to keep and what to move. I did see the formula breaks when you delete the yellow cells, but I didn't know what you wanted to replace them with. Do you want them to go blank for those dates? If so you could Conditional format the text of those dates to match the background color. I'm still at a loss on what you want.

[JohnTopley]

Keep it simple: Copy/Paste values and then delete the required dates.

[theirlaw]

Hi,

I thought you'd skip ahead 14 days if you hit one of those vacation dates. I think you are better off to simply do a manual date input for the ranges below those vacation dates. I don't really know what to keep and what to move. I did see the formula breaks when you delete the yellow cells, but I didn't know what you wanted to replace them with. Do you want them to go blank for those dates? If so you could Conditional format the text of those dates to match the background color. I'm still at a loss on what you want.

Sorry for confusing you. Yes, I do realise that manual data input would be possible and only take a matter of seconds. However, I thought that there may be a more graceful way to handle this sort of situation.

Essentially, what will happen is my class in column B (ESC111-08) will have a holiday on June 6th. This means that they will not receive the language instruction for unit 12B, and that on the next available day (June 8th) they will need to be taught this.

Am I over-complicating things by wanting to automate this?

[JohnTopley]

In my view: YES - for something that could have been addressed in the time it takes to post to this thread!!!!

And given the simple formula, it is easy enough to quickly replicate if required.

[theirlaw]

Thanks for the help - I've finished the timetable using manual data entry as suggested.

Just for curiosity's sake... what would it take for the functionality I've described above to be implemented?

I guess it would need some kind of IF statement, to lookup which dates are public holidays, and then some additional behaviour to handle dates that are matched?