Need Calendar Help

[sella_rogers]

School Dist Calendar.zip

I need to input school district calendars in a timeline format in excel by week. We have been manually doing it and I know it can be done with formulas. Please help!

We need to track the days in service and the holidays/breaks, etc., by blacking them out. Is this possible?

I can't get my sample to upload.

We have a calendar from 7/27 labeled Monday, Tuesday, Wednesday, Thursday, Friday and then the next week and so on.

Here are some dates for the first entry:
Start of School End of School Holiday Fall Break Holiday Thanksgiving Break Winter Break Holiday Spring Break Holiday
7/27/2015 6/7/2016 9/7/2015 9/21/2015-10/2/2015 11/11/2015 11/23/2015-11/27/2015 12/21/2015-1/8/2016 1/18/2016 3/21/2016-4/1/2016 5/31/2016
http://i52.photobucket.com/albums/g2...0Calendar2.jpg

[p24leclerc]

It should be possible but we need your workbook to work on it.
Can you try again to upload it?

[sella_rogers]

I added the file in a compressed file. See above. Thanks in advance!

[p24leclerc]

Here is your file with some modifications and a macro that will do exactly what you asked.
First, you MUST enter your holiday in date format with 2 numbers for month and day like 01/25/2015-01/30/2015.
For Holidays that are only one day, the cell content is usually considered as a date and its format is OK
but for Holidays where you enter 2 dates, you must enter the month and day with 2 numbers.

Then, your description in column A must match exactly in both sheets.

I hope it is not an issue for you to use a macro.
I did this because formulas would have made your file too big and slow and also because
the formula would have been very comlicated.

[sella_rogers]

Thanks for your help but I am having a problem with adding/changing dates.

I plug the dates in on the spreadsheet and they do not calculate over to the other spreadsheet. Is there a way to update the macros? Not sure how they work since I am used to working with formulas.

Thanks in advance!

[p24leclerc]

See attached workbook.
I created a command button that will run the macro when you click on it.
It first resets the interior color of the full table then it scans that Holiday dates sheets to black out
holiday's dates in the Timeline sheet.
You can see the VBA code by entering into the Visual Basic Editor which can be done by clicking on the ALT + F11 keys
at the same time.

P.S. if you have issues like dates are not blacked out, it can be the date format of row 4 done by the macro before doing the searches. It might be system's specific. So you might have to play with it in the code.

Hope this helps

[viperezai]

Hi,

I have a question for p24leclerc. I used your format and I really liked it. I appreciate your help with this. I'd like nevertheless to add different color in the timeline for each column you have in the input table (Holidays Date). Such as differentiate between Thanksgiving break in orange for example in the Holidays Date table and Fall break in black.

Thanks in advance for your help.

See attached workbook.
I created a command button that will run the macro when you click on it.
It first resets the interior color of the full table then it scans that Holiday dates sheets to black out
holiday's dates in the Timeline sheet.
You can see the VBA code by entering into the Visual Basic Editor which can be done by clicking on the ALT + F11 keys
at the same time.

P.S. if you have issues like dates are not blacked out, it can be the date format of row 4 done by the macro before doing the searches. It might be system's specific. So you might have to play with it in the code.

Hope this helps

[p24leclerc]

See the attached workbook.
The macro will apply the background color you'll put in the Holiday Dates sheet for every column.
You MUST put a color in this sheet otherwise the Timeline dates will be WHITE.
See the Holiday Dates sheet for an example.
It was just a small modification to one line of code to apply the Holiday Dates cell color to the one in Timeline sheet.
Regards

[viperezai]

See the attached workbook.
The macro will apply the background color you'll put in the Holiday Dates sheet for every column.
You MUST put a color in this sheet otherwise the Timeline dates will be WHITE.
See the Holiday Dates sheet for an example.
It was just a small modification to one line of code to apply the Holiday Dates cell color to the one in Timeline sheet.
Regards

Hi,

I have been playing around with the following sheet just to test and understand it. I unfortunately don't understand why some of the dates are not correctly represented on the timeline on my sheet. Do you have an idea why? @P24leclerc

thank you!

Schedule Test.xls

[p24leclerc]

The issue comes from the fact that, as surprising as it might be, Excel will not find a date
if it is not properly represented in the cell.
That's why some was working and others not.
I added some comments in the code so you can understand what it is doing.
Hopes this one will work well for you.
Regards and sorry for the inconveniences.

[viperezai]

Thanks a lot! Appreciate the help :-))