Excel PTO Calendar

Hi All,

I need some help creating a multistep PTO calendar where I create a form that will input the data into rows and then transferred over to a calendar view. I have basic to moderate Excel experience and know this is a bit out of my league, but would like to learn how to do this.

Can someone please let me know how I should get started on this? I'd like to have a dropdown list of employee names and the start/end dates of when they're off.
We could skip this form step altogether, but it would be nice to have.

In short, I'd like to have data, or a list of PTO ranges to show up in a 12 month calendar view (that also archives/shows future years IE - 2014-2020)

Example would be:

Employee Start-Date End-Date

Employee Name	Start Date	End Date	Type
Bob	8/1/2015	8/5/2015	PTO
Billy	8/2/2015	8/2/2015	Sick

There are two annual leave calendars in this thread (slightly different):

http://www.excelforum.com/excel-new-...-calendar.html

and here's the thread that gave rise to the first, so you can put it in context:

http://www.excelforum.com/excel-form...e-to-tab2.html

Hope this helps.

Pete

Hi Pete,

I don't have much at the moment, I'm taking a stab at creating a embedded UserForm or Data Form but I'm not sure if that's possible and had no idea how to start the calendar aspect. (but they can just deal with putting it onto the list)

Your calendar/provided worksheet looks like its a very good start, but I'd like to input date ranges as well instead of day by day entry.

I'll take a look at your worksheet/formulas and see if I can take anything from it, but the formulas are a bit overwhelming for a beginner like me

Thanks for all your help.

I'm just working on another calendar at the moment for another poster. This one will allow date ranges to be entered, and will expand the entries automatically so that there is one per day which fits in with the requirements of the calendar. I've done others like this recently (the other calendar is quite old).

Pete

Thanks Pete!

I'll stay tuned. I do like the fundamentals on this one and look forward to seeing what you come up with. In the meantime I'll keep trying to understand the formulas you have built on the current one I'm looking at.

Really appreciate your help.

I've modified the leave calendar file so that you can enter date ranges (or just one date), and these are expanded into individual dates automatically (file attached).

To use the file you just complete entries in columns A to D of the Leave_booker sheet, i.e. Name, leave type (available from the drop-down), start date and end date (optional). Formulae in the green columns have been copied down to row 100, but these need to be copied down to at least cover your data, so copy them further if required. Formulae in the blue columns (copied down to row 300 in the file) need to be copied down until you have hyphens showing, to ensure that you have captured all the data.

In the Calendar sheet you can select the Month and Year using the drop-downs in K5 and K6, and the display will adjust automatically. It will show up to 12 different bookings on each day. For 30-day months which start on a Saturday, or 31-day months which start on a Friday or Saturday, the calendar will show the "orphan" days from the end of the month at the top - examples can be seen for May and August 2015.

Below the calendar is a summary of leave taken during the month for each employee, broken down by leave types, which are shown on row 93, and this list is used for the data validation drop-down on the Leave_booker sheet. If you wanted a drop-down for employees' names, then you can use the list here as the source.

Hope this helps.

Pete