(MS Excel) I want to make a small employee vacation chart. How?

Question

Suppose I have one year, from January 2007 to December. I asked my employees for their desires vacation timings so we could be able to find replacements and ensure the workflow and to avoid any conflicts. I have an Excel sheet now with "from - to" dates, e.g. Jennifer: 15 Aug 07 to 13 Sep 07, John 1 May 07 to 13 May 07, and so forth. I was able to make a basic chart, in which one axis was the day and the other was the month-year. We need such visual representations to make it easier to read the charts. My purpose is to avoid vacation conflicts, so I need to see if lines are going to be parallel to reach other. My chart doesn't have parallel lines. How can I make them? But more importantly, how can I represent a vacation that is within one month? I was able to create a visual representation of 15 Aug to 13 Sep, but that was because they were in separate cells. 1 May to 13 May, that's in the same cell, so it didn't work. How can I represent a vacation time that's in one cell? Thanks in a

..in advance.

That's marvelous, Rez. It's close to what I'm asking, although not exactly what I need (but I did copy it, I needed something like that, thanks). I'm wondering, is there a way to represent all the vacation times in one chart (bar chart, line chart, etc.)? With 260 rows, I imagine it's not possible, so I figure we must use only the vacation days, not all the year days. Is that possible at all?

Answer 1

I assume you count full-day vacations and not less than that?

If so, considering there are about 260 "working" days, the chart would have to be 260 columns or rows wide. Here's what I use at work:

In column A (A2) I put the first Monday of the year (1/2/06). Then, in A3 I put the formula =A2+1 and the same for A4, A5, and A6. In A7, I put =A6+3. I then copied A3-A7 and pasted then down the worksheet until all dates through 1/1/07 were shown. Format the column as you like.

Then I put the names in row 1 starting at B. So B1 is "Mike", B2 is "Joe", etc. I formatted it so that the names were vertical.

I then identified each holiday date by making the background yellow. Finally, to put in the vacation, I just changed the background of the days for a given person to red. This allowed an easily changable system that gives up to a full year view of the vacation schedule.

I've posted a sample at:

http://www.chuqgunabeads.com/assets/images/vacSample.xls

Answer 2

I've got a spreadsheet that can do what you want. It has Names at the top with start and stop dates for the vacations. Then the names down column A with dates for each week and whether or not each person is on vacation is calculated in the cells to the right. It's a little too complicated to detail here, but if you'd like I can send you a copy. Just send an email to [e-mail]