Speaker 1: Hi, I'm Peter Kalmström of kalmström.com business solutions. Team calendars is a popular subject. Almost every company has one, almost every team needs one and they can be done in so many different ways. I'm going to show you one way of doing it in Excel. First of all I want to show you the built-in ones. If I go to new here and search for calendar you'll find a lot of different calendar templates but I want to show you a different version than these of course. But if one of these fits your needs that is of course perfect and you should check them out before going down doing it manually as I'm gonna do. So what I want is a monthly calendar with the staff on the columns here and then I want the days on the rows. So I want to have a month template and then I want to create a new one for each month and fill in the information for my staff there. Of course there are other ways of doing this in Outlook and in Planner or in SharePoint but this is just one do-it-yourself in Excel. So first of all we want to have the staff and then we're gonna have the people. I just have two people here that I'm gonna be working with as examples and of course I double click here between the columns to make that wider so that it all fits. And then I'm gonna start with the first date that I'm gonna have. So I'm gonna write that in Swedish format which is the one I know best. So that's the 1st of January 2018 and of course I have this set on American now so that shows up like that all right yeah and the time format is American. Then on the C column there I'm gonna put the same dates plus one. So I'm just gonna do B1 plus one and that of course gives me the next day. And then I'm gonna fill this out all the way out to AF which is corresponds to the 31st. And to get to the beginning again I'll hold down the ctrl and the shift key and press the left arrow which allows me to select everything and then I double click so that it all fits. All right now I have all the way from 1st of January until the 31st of January. As you see this looks very unreadable though so I'm gonna change this a bit. First of all I want to put the same thing in the row under. I'm just gonna do equals and the cell above and then I'm gonna fill that out all the way again. Right that didn't make much of a difference that certainly didn't make it more readable but what I'm gonna do now is that I'm gonna select all of this all these dates here and go all the way to the right ctrl shift right arrow and I'm gonna format these numbers. I'm gonna show only the weekday here. I do that by entering DDD in the custom format here. That shows me the weekday that's what I want in the first row. If I do DDD with four D's and I get the full day. But I want to keep it readable so I'm just gonna go with the three first letters of each weekday. I'm gonna do the similar thing ctrl shift right arrow to get all these dates and I'm just gonna show the weekday or actually the day number within that month. So I'm gonna format the number again go into custom and then write just D. I write DD and I get the 01 format which might be nice. Next I'm going to manually go in actually let's not do that yet and I was gonna manually highlight the weekends but I'm gonna do that later because I'm gonna start by a template first. The thing I want to do in this template first is just put some lines in here and the easiest way of doing that is to use the draw border. So now I can just draw a border here really nice like that and then draw a border there all the way out. As you see it doesn't really fit on my screen so I'm gonna do one more thing with these quits the drawing now and I'm going to center all of this let's do that center vertically yeah and then I will make it slightly smaller and I'm gonna do that by double-clicking. So now it actually fits on screen on my resolution at least. I think this looks rather okay as a template now. The next thing I'm gonna do is name this sheet template and then I will go and drag and drop that template to the left. There we go. So now I have template 2 and I can rename that to Gen 2018 like that and I can pull it in order like that so that's the first one. Let's do a February also just to make this clear. So again I'm take the template and pull it over there and then make that February. For February of course I want to change the start date. Just do that in one cell and that's gonna be 2018 0 2 0 1 right and then of course I need to make sure that it all fits. Now I'll go in and just color code weekends. I'll do that by yellow then I'm actually going to hide the rows that I don't need. I'm just gonna do shift control down arrow and then right-click and hide these rows and the same thing I'm gonna do with these columns I don't need. So again select and shift control right arrow and then hide all of those columns. I should perhaps have done that in the template but I guess you get my drift here. Now for actually scheduling things in the calendars I'm gonna recommend this method. So let's say Stina she's actually on vacation here for this period so I'm gonna select that period when she's on a vacation just type in vacation and then enter. I'm still have the whole range selected and then I'm gonna merge and center that and then I'm gonna do some color coding for that. So let's put that on green for example and then we can go ahead and do Calcula he's gonna be at NASA for those two days. Again I'm gonna merge and center and then color code that that's a you know some kind of important project there so I'm gonna color code that in blue maybe have a white text to make that more visible that looks good. Now if I want to copy this entire he does that every week let's see it's Monday and Tuesday at NASA and I can just right-click that and copy and then do the same next week and ctrl V to paste that ctrl V to paste that. So there I've shown you how to schedule things and I think that looks rather nice. One further thing that you might want to do is learn how to erase these things of course you can always go back to the template but what if that NASA thing gets cancelled there. Best way to do that if you just press delete you see you still got that emergent center and the color coding and all so that doesn't do it so if you want to clear that out I would suggest taking an empty day copying the format and then just erasing that and then you get blank day. So that shows you how to build a team calendar from scratch in Excel. Thank you for watching this demonstration.