Excel flexibler Urlaubsplaner mit intelligenten Funktionen Teil 1, inkl. kostenloser Vorlage

holidays. I have to remove whether the mandate is for a weekend or a holiday now I'm going to insert a top above the center. I like the frame, three right mouse click and I say insert cells and here I'm going to write a formula 1 that tells me if my day is a weekend or a holiday like that. So the formula first comes to ours, which is already a weekend where I check the day of the week here if it is. greater than 5, that is, Saturday, Sunday, then the result here should be in this part of the formula 1 and otherwise a zero and here we are looking for a reference to a date So this is my search criteria for a holiday: the date and list of holidays.

I'm looking for it in the first column. In this case we only have one and I'm having a hard time here because I want to have an exact match and if nothing can be found here, then it was a construction error. to be returned, then this area of ​​the formula should result in a zero and otherwise a 1, that is, if my appointment occurs on a Friday, it should result in a 1 and these two values ​​should be added, that is, if it is only a weekend once would get a weekend and a holiday then I would do it on the second and if there is no weekend there is no holiday then a space now once which of course is still formatted as date , now let's go to the numbers here and the fourth as standard and as you can see the CDV was perfect a few days later, it's a Saturday and a holiday so there's a second one here, it's just a weekend so there's a one and in a completely normal lemma there is a 0. 3 now of course I want to use this formula to transfer the entire area of ​​my town, now I would like to write a little help x1 here at the end.

I think so, now I will go to my first appointment here and with control. my right arrow key I'll go all the way here on my help line write me here a little x2 and extra time right mouse button I go back to the beginning now I go into my formula 1 and with ctrl shift and the right mouse button frame button all the area here and then with control I transfer the formula here to the end. Now we will do the rest with data verification. I click when I see a 0 0 field in it, I go to data tools.

Check the data and as you can see we have already passed the absence type here but now it should show if it is up and make sure that just fixed the line again it is equal to zero so only if we are up and then it will be you should show the absence unit and activate the check mark here, then the change will be applied to all cells with the same settings. Ok, you can see that it worked, so with my help, I can now hide it. I do it in no. fakire right click and hide the days and what I want to do next to give it a little more structure is I have to differentiate the months from each other a little bit.

That was me where I marked the entire area again with control source and the right arrow key then goes to the conditional formatting, a new regulation position with the help of a formula and here I enter the formula 1 so that the restore returns us the remainder of a division, the first thing we deliver here is the month, so that's the number and that's the divisor and in case this results in a remainder I want the cell to be light blue, okay, like You can see, it worked, the February blue color blockers didn't happen again, however we now have two problems: firstly, the format has now overwritten our weekend and holiday format and secondly, it didn't.

We have transferred to our holiday area. Now we can fix this here using conditional formatting, so let's manage it in an interesting way. Next, let's show all the rules in this worksheet and first. We all transfer the rules to our entire vacation area. Sometimes, as you can see, it worked, but here too, the rules that have flexible schedules and colored vacation days have been overwritten, so now I'm moving these formulas right to the end. Finish, click Apply, and as you can see, now the formatting is there again, so we're almost done. What I would like to do to improve clarity a little more is insert lines here.

Now I click on my table here. go to the tables and do this format here. I would also like to do the same for my date range. I'd like to have my entire range here again and I'll also insert the frame lines below. Still, I would like to expand the title a little. and put it in bold, something like this. Last but not least, I have to quickly put the example up front so it says vacation. I was thinking that you can enter entire holidays in days, so here, for example. , 30 days should be here Then the remaining vacation is displayed, that is, the total vacation: all vacation days taken for this.

Then I write the following formula 1 like this and the formula looks like this over the teeth. If it works, I count everything from this line. and then I subtract this from the total vacation fund for. At the same time, I write the following formula 1 about the teeth, if function. Then I count all the gs again, so I make these headings even bolder, I make this column a little smaller, the last thing I did. What I want to do here is I also do this record formatting if they are less than 0 are marked for this she again went to the conditional formatting less than 0 and then there should be a light red padding so if I entered too many we here now I can do so then the value here would be red and the last The next step is to fix the view To do this click here on the first vacation field Go to the view window to fix it.

The vacation list is ready. Now I would like to show you again what happens when we select a new year. Then come up here. And as you can see, the calendar adjusts automatically. Then you need to find the vacation days for your federal plan. indicate for the following years and then write them on the list here. The list expands automatically and a new employee could easily work in a smaller city. You can then easily add a new employee by clicking on the next time off and entering something. and all the formatting and formulas will of course carry over to the next line, so I hope you enjoyed the video.

If so, I would be very happy. If you have the approval, I wish you a lot of fun with the neighbor. or as I said, the finished template for free from my website under the link can be found in the info box, so I would like to thank you very much for looking until next time, bye.