Holiday Planner vba

Hi all,

I'm working on a holiday Calendar for 2016 (and I want to make it easy to update the years).

See attached an example of what I want to happen.
Attachment 442980
You have 2 Sheets (Vacation) and ( (2016)

I want to be able to update the (2016) sheet as soon as you enter 2 dates in the (vacation sheet). Each employee has a number of days they can take (cells L4-L8)
I want the code to be able to see if there is a weekend in the calendar. and if it is, it doesn't count the days of weekend in that vacation (I now have to enter it manually) It also needs to update the calendar if you take a half day out)

In the file attached, I manually update the calendar so you can see with colors who is taking a vacation. this is with conditional formatting, so it just needs to enter the name of the employee taking the day off.

Can you please help me with completing this, and maybe help me get started with the code?

I know this might not be easy, but I started this project to help me learn VBA more than I do at the moment. so if you help me out, can you please also give some information what the code is doing when?

any help is greatly appreciated, and I will add rep for anyone who is helping me on this..



please find the completed planner here :D

Hi,

For these sorts of calendars it's more usual to have all the dates of the year across all 366 columns with the columns very narrow with the month name on a separate row above the day numbers. The dates will be formatted to show just the day number but the cell will contain the underlying date number

Then to the left of your calendar columns you hold the Employee names and the Start & End dates of their vacation plus any other stuff. Then it's a simple matter to conditionally format the day columns to be painted if the date on the date row for that column is between the Start/End Dates on that row.

I think you're making life more difficult than needs be, particularly of you're thinking of using VBA code.

If you want an example of the above then post back or just look at one of the many existing Excel templates that deal with this stuff. No point in re-inventing the wheel.

Hi Richard,
Thanks for replying,

I know these files exists, but I find them a bit of a mess to be honest. I want a sheet that with just one glance, you can see who took a holiday on what day over the course of the entire year.
And as I said, i knew this would be difficult. I have some experience coding in vba, and it's a lot easier if it just have to look in one direction instead of both the column and row. So I would learn a lot out of just making this file.

Also, i'm not expecting anyone to make this file. I'm just asking for some pointers on how to make happen what I want.

Stielo

Originally Posted by stielo

Hi Richard,
and it's a lot easier if it just have to look in one direction instead of both the column and row.
Stielo

I don't understand this comment, particularly since your current example means you DO need to look in 12 blocks of rows for each month and of course across the columns. i.e. doing exactly what you say you don't want to do.

This seems to me far more confusing than the solution I suggested which means that you just have to look across a single row (i.e. in one direction) for each employee. What could be simpler?

Originally Posted by Richard Buttrey

I don't understand this comment, particularly since your current example means you DO need to look in 12 blocks of rows for each month and of course across the columns. i.e. doing exactly what you say you don't want to do.

This seems to me far more confusing than the solution I suggested which means that you just have to look across a single row (i.e. in one direction) for each employee. What could be simpler?

I just meant I want the challenge of using some difficult coding for the learning experience. I know there are easy solutions. but I would learn more out of using the hard and confusing way.

I don't like simple ways when I want to learn about coding.

Even if you want to use code to do this as a learning experience I'd still be inclined to use the layout I suggest (see attached) and write the code to create the conditional formats that I've manually entered.

That will test your coding ability to identify ranges, compare dates and apply conditional formats.

Hi,
I like how the conditional formatting works, but do you have any Idea how to exclude the weekends and national holidays?
Is there a formula in excel that can do this, or do i need to add days in a row to make this happen?
I'll work out a file with a bit of the file you made and some other stuff I want added, If I have an updated version I will attach it to a post here.
This might take a few days or so. But thankfully my boss wants me to learn more about vba and they don't mind me doing stuff like this while working. (as long as my other stuff gets done that is :p)

Hi,

Yes it's easy enough to exclude weekends and holidays.
To identify weekends you'd use a helper row above the dates somewhere and use the function

=Weekday(A1,2)

This will return the day of the week for date cell A1 starting with Monday as zero and Saturday & Sunday being 6 & 7.

For holidays you need to create a list of statutory holidays and test each date against that list. e.g
=IFERROR(MATCH(A1,holidays,FALSE),"")

'holidays' is a list of holiday dates and where A1 is a date that appears in the list then the result will be a number - (the position in the list of holidays) so that you can use this fact as another test.

In the attached and just for testing purposes I've added a couple of holiday dates on the new Holidays sheet.

I've put those two formulae together in rows 2 & 3 and used Row 1 as the consolidation of both so that where there is eithe a weekend or a holiday row 1 returns a 'Y' and this I've no incorporated in the Cond Format for Steve on row 6 - I've also changed his From/To dates just for testing so the CF in row 7 is now

Formula:

Please Login or Register  to view this content.

Thanks,

I'm now actually using a new format(different than the one you use, but still easy to format)


I made the calender transferable to new years, just clean the data of the vacation sheet and it's good to use,
it transfers automatically between leapyears (2 sheets which switch when it's not a leapyear, using vba to show the sheet when criteria is reached)
Also checks the date and applies formatting, so you know where you must look on the calendar.


The thing i'm now trying to do is creating an insertion sheet (Vacation) where the employees can insert the dates they want to have a holiday and I want it to show on the calender (Column D)
the problem is, it doesn't show how it should be (like column E)

See the attached file for more details

Please Login or Register  to view this content.

Hi,

Since you're using this as a learning process (post #3) and rather than me doing it for you here's a couple of observations.

1. Nowhere in your column D formulae are you attempting to recognise weekends or holidays. Check my previous posting and attachment which shows the basic syntax for building this in using the Weekday() function to identify the day of the week from 1-7, and matching any date to a list of statutory holiday dates and if there's a match ignore that day as a personal holiday.

2. I think you're overcomplicating this leap year stuff. With the date 1/1/2016 in A1, if you enter =A1+1 in A2 and copy it down to A366 you'll find that Excel already knows 2016 is a leap year and A60 will read 29 February. In non leap years A 60 will read 1st March. The only correction you need is on A366 with a formula like

Formula:

Please Login or Register  to view this content.

I can't see any point in maintaining two calendar sheets.

So, I finished a great calendar, and it works perfect for me. thought I should upload it here anyhow, and mark this thread as solved.

Richard,I only used your

Please Login or Register  to view this content.

. but I found this need thing called sumproduct which is quite handy.

I use the 2 tabs just for layout purposes, it looks much cleaner, and the formulas are just copying the other sheet. so not much different there.
Feel free to use this planner. and if you want small adjustments (like more names or stuff) and you can't do it yourself, feel free to ask. cause it's pretty fast to do it.

Please Login or Register  to view this content.