Peter Kalmström shows how to create a custom team calendar in Excel: www.kalmstrom.com/Tips/Excel-Calendar.htm
Hi I'm Peter Kallstrom of Kallstrom comm business solutions.
Team calendars is a popular subject.
Almost every company has won, almost every team needs one and they can be done in so many different ways.
I'm gonna show you one way of doing it in Excel.
First of all, I want to show you the building once the 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 words, but if one of these fits your needs, that's 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 wanna have a month template and then I wanna hack 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 and 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 access 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 had know best.
That's the first 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, unless II column there I'm gonna close at the same date, plus one so I'm, just gonna do b1, plus one I know.
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 in 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 first of January until the 31st all 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 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? Instead, I'm gonna select all of this all these dates here and go all the way to the right control shift, the 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, I do D D, D D, with four days and I get the full day a 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 and ctrl shift right arrow to get all these dates and I'm.
Just gonna show the weekday or actually the day number within that, not so I'm gonna format, the number again go into custom and then write just D all right: D, D and I get the zero one format which might be nice right 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 templates.
First is just put some lines in here and the easiest way of doing that is to use the draw border now.
I can just draw our 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 quit the drawing now I'm going to Center all of this.
Let's do that Center vertically yep 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 two and I can rename that to Gen 2018 like that and I can pull it in order it like that.
So that's the first one.
Let's do it 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 going to be 2018 zero, two zero one 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 do that by Yello, then I'm actually going to hide the roads that I don't need.
That's just gonna, do shifts, 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 it's Tina.
She is actually on vacation here for this period, so I'm gonna select that period when she's on 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 cat Akula he's gonna be at mouse up 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, ctrl V to paste that ctrl V to paste that so they have shown you how to schedule things.
I think that look should 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 but if that NASA thing gets council there best way to do that.
If you just press Delete, you see you still got that emergent Center and the color coding you know so that doesn't do it.
So if you want to clear that out, I was just 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.
- Enter the names of the team members in the A column, from A3 and down.
- Add the date of the first day of the year in the B1 cell.
- In C1, enter =B1+1.
- Fill out to AF1, which then will show day 31 in the month.
- Double-click between the columns to adapt the cell size to the content.
- Open Outlook and click Calendar to the left of the screen.
- Right-click on your new calendar.
- Select Share and then Calendar Permissions.
- Click the Add button.
- Select the user that you would like to view your new calendar.
- Click on the Add -> button and click OK.
- Open Google Calendar.
- On the left, click the name of your new calendar.
- Point to the shared calendar and click More. Settings and sharing.
- Choose an option: ...
- In the permissions box, click the Down arrow. ...
- Click Send.
Click Share Calendar at the top of the window. Click the Add... button, and enter the Last Name or Distribution Group of the person or group with whom you would like to share your calendar. Select the person's name from the list and click the Add → button.
Select the Sheet1 tab. On the Tools menu, point to Macro, and then select Macros. Select CalendarMaker, and then select Run to create the calendar.What is the formula for automatic calendar in Excel? ›
Select a blank cell for displaying the start date of month (here I select cell B6), then enter formula =DATE(A2,A1,1) into the formula bar, and press the Enter key. Note: In the formula, A2 is the cell contains the certain year, and A1 is the Combo Box contains all months of a year.Is there a calendar control in Excel? ›
Insert a calendar control
To insert it in your sheet, just do the following: Go to the Developer tab > Controls group, click Insert, and then click the More Controls button under ActiveX Controls. In the More Controls dialog window, find the Microsoft Date and Time Picker Control 6.0 (SP6), select it, and click OK.
- On your computer, open Google Calendar.
- On the left, next to "Other calendars," click Add other calendars. ...
- Add a name and description for your calendar.
- Click Create calendar.
- If you want to share your calendar, click on it in the left bar, then select Share with specific people.
When you create a family on Google, a calendar called "Family" is automatically created. Anyone who joins your family will see the family calendar when they open Google Calendar on any device where they're signed in. Anyone in the family can see, create, edit, or delete events on the family calendar.How do I create a team calendar in Outlook? ›
In Calendar, on the Home tab, in the Manage Calendars group, click Calendar Groups > Create New Calendar Group. Type a name for the new calendar group, and then click OK. Under Address Book, choose the Address Book or Contact list from which you want to pick members of your group.
- Asana. Asana is more than a shared calendar app; it also makes it effortless to manage team projects and tasks. ...
- Calendly. ...
- Google Calendar. ...
- Microsoft Outlook. ...
- Teamup. ...
- iCloud. ...
- Thunderbird Lightning Calendar.
Employees can use shared calendars to manage their own assignments and appointments, while team leaders can use group calendars to coordinate meetings and other tasks.How do I create a Microsoft calendar team? ›
- Once within a given Teams Channel, click the + Tab.
- Within the app search bar, type in Calendar and then choose Channel Calendar App.
- Give the tab a name and click Add.
- You will now have a Channel-specific calendar added as a tab to your channel, displaying only the events from that Channel.
In Calendar, on the Home tab, in the Manage Calendars group, click Calendar Groups > Create New Calendar Group. Type a name for the new calendar group, and then click OK. Under Address Book, choose the Address Book or Contact list from which you want to pick members of your group.How do I add groups to my calendar? ›
- Open Outlook.
- Click on Calendar.
- On the left-hand side right-click My Calendars.
- Select New Calendar Group.
- Name your Calendar Group.
- Right-click on the name of your new Calendar Group.
- Select Add Calendar.
- Select where you want to add the calendar from.