Step 1 – Prepare The Calendar Layout
Before we delve into creating the schedule, You must first create the outline of the calendar first in which you’ll implement your formulas.
Steps
- Place the date and month on the sheet.
- Set to date and month to be dynamic to today’s date.
- Our calendar will follow the weekdays starting from the Monday format.
Step 2 – Add Formulas to the Calendar Outline
Steps
- Select cell D4 and enter the following formula to extract today’s date:
=TODAY()
- Select cell H4 and enter the following formula:
=MONTH(D4)
- Highlight cell B7 and enter the following formula:
=DATE(2022,$I$4,1)-WEEKDAY(DATE(2022,$I$4,1),2)+1
Formula Breakdown
- DATE(2022,$I$4,1)
⮚ Returns the date in the proper date format. The month is from cell I4, the year mentioned is 2022, and the date is 1.
- DATE(2022,$I$4,1)-WEEKDAY(DATE(2022,$I$4,1),2)+1
⮚ Subtracts the weekday from the date value. It will make sure that Monday stays at the front of the calendar.
- Select cell C7 and enter the following formula:
=B7+1
- Drag the Fill Handle from B7 to H7 to fill the cell range with dates starting from October 29 to September 4.
- Choose cell C8 and enter the following formula:
=B7+7
- Drag the Fill Handle to cell B12.
- Repeat the same process for the rest of the cells to fill all of your cells with the weekdays in a month.
- Right-click the cells and select Format Cells from the menu.
- Select the Number tab.
- Enter Custom options and select the Type field.
- Enter dd only so the user only sees the date’s day portion in the table.
- Click OK.
- Your calendar now has the dates in a double-digit format.
- We have the dates from the previous and next month, which we want to avoid. So, you need to conditionally format the values in your table.
- Select the whole calendar.
- Go to the Home tab > Conditional Formatting > New Rule.
- In the Edit Formatting Rule window, select Use a Formula to Determine Which Cells to Format.
- Enter the following formula:
=MONTH(B7)<>$H$4
- After entering the formula, click the Format button.
- In the Format Cells dialog box, click on the Fill tab.
- In the Fill tab, choose white as your fill color.
- Switch to the Font tab and choose white as your font color.
- Click OK.
- The dates from the other months are now omitted from the view.
Step 3 – Enlist All Scheduled Programs
Steps
- The list of the programs in September is list with their associated scheduled dates.
- Modify the existing table by adding an extra cell on the right side of each date. You’ll use these cells to store event information.
Step 4 – Link Programs with Calendar
Using functions like TEXTJOIN and FILTER you can now link the calendar’s dates to specific events.
Steps
- Select cell C7 and enter the following formula:
=TEXTJOIN(CHAR(10),TRUE,FILTER(plan[Program],plan[Date]=B7,""))
This formula will add the event associated with that date into the cell.
Formula Breakdown
- FILTER(plan[Program],plan[Date]=B7,””):
⮚ This formula will look for the value in cell B7 in the table plan’s program table header column. If a match is found, it will present the whole row of information. Otherwise, it will extract a blank cell.
- TEXTJOIN(CHAR(10),TRUE,FILTER(plan[Program],plan[Date]=B7,””))
⮚The TEXTJOIN function will join a separate line from the output of the FILTER function. The CHAR(10) [New line] here acts as the delimiter between two or more text lines.
- Drag the Fill Handle to cell C12.
- The cells are currently in a blank state because there is no event associated with the dates mentioned here.
- Repeat the same process for the subsequent cell.
- Select cell G7 and enter the following formula:
=TEXTJOIN(CHAR(10),TRUE,FILTER(plan[Program],plan[Date]=F7,""))
- Drag the Fill Handle to cell G12.
- Repeat the same process for the other weeks to complete your calendar with its schedule.
Read More: How to Make a Daily Schedule in Excel
Download this practice workbook below.
Related Articles
- How to Create a Weekly Schedule in Excel
- How to Create a Recurring Monthly Schedule in Excel
- How to Make an Hourly Schedule in Excel
<< Go Back to Excel for Business | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
This was super helpful for what I was trying to do! Thank you so muchn
Hello, Andy W!
Thanks for your appreciation.
Regards
ExcelDemy
Hi,
I want to create a monthly excel attendance working schedule with formulate to calculate hours worked based on time in time out everyday for the whole month with daily hours totals excluding 1 hour lunch, total for the week and total for the month for each of the 10 employees. I have an example of one that was created with option of choosing time or off or on leave from the drop down arrow without typing. I like that but want to know how I can create mine from scratch.
Greetings,
I understand your query. You asked about attendence sheet for both monthly and weekly basis. But my suggestion will be to go on with the monthly basis assesment. In this way your job will be much smoother. Secondly I provided a demo sheet that actually contains the attendence with time picker drop down, and summation of total hour of work in a month.The time picker need to have a fixed time and they are specified below the sheet.You just need to enter the month and year in the beginning and then you can enter the in time and out time using the time picker.
The download file can be accessed from the link below,
https://www.exceldemy.com/wp-content/uploads/2023/04/Attendence-SheetMonthly.xlsx
Thank you for this
Hello Michael,
You are most welcome. Keep learning Excel with ExcelDemy. We provided a formula to auto populate events from entry data to calendar sheet.
Regards
ExcelDemy
Hi Rabayed
Thank you so much for the help
Can I have entries of an event in the first sheet and have it automatically populated in the calendar sheets as per the event
Can a formula also combine cells according to events that are more than one days
Thank you so much
https://docs.google.com/spreadsheets/d/1bt-FUhrareuYc3lH1QuI68TOfSezkTTb/edit?usp=sharing&ouid=108748915799897862730&rtpof=true&sd=true
Hello Michael,
You are most welcome. You can auto populate events in the calendar sheet from your entry data sheet but Excel formulas has limitations over handling multiple overlapping events. Formulas will concatenate multiple events into a single cell, but each overlapping event will be separated by a line break or other delimiters.
You can use the following formula in your event cell to auto populate events.
=IFERROR(TEXTJOIN(CHAR(10), TRUE, FILTER(‘Entry Data’!$B$2:$B$100, (‘Entry Data’!$D$2:$D$100 <= B10) * ('Entry Data'!$D$2:$D$100 + 'Entry Data'!$C$2:$C$100 - 1 >= B10) * (‘Entry Data’!$E$2:$E$100 = 8) * (‘Entry Data’!$F$2:$F$100 = 2024))), “”)
Based on Month please change the Month Number and Cell reference for each month and each cell.
If an event spans multiple days, the formula will check if the current day falls within the event duration and will display the event in the cell. If multiple events occur on the same day, they will all be concatenated in the same cell.
Download the Excel file:
Monthly Event Chart
Regards
ExcelDemy
Would there be a way to look at this as a calendar quarter or year?
Hello Destiny Rivera,
Yes, you can modify the schedule to display by calendar quarter or year. You would need to adjust the date formulas and ranges to reflect quarterly or yearly data. For quarters, use a formula that groups dates into one of the four quarters of the year. For a yearly view, adjust the schedule to display the dates and events by the full year.
For Quarters:
Use the MONTH function to categorize dates into quarters:
=CHOOSE(MATCH(MONTH(A2),{1,4,7,10}),”Q1″,”Q2″,”Q3″,”Q4″)
This formula checks the month of the date in cell A2 and returns “Q1” for January to March, “Q2” for April to June, etc.
For Year:
You can simply extract the year from a date:
=YEAR(A2)
Regards
ExcelDemy
hello all ,I am horrible at excel however I need to create a auto rotating two week schelude , for my company with three shifts , and two departments is anyone able to help ?
Hello Bryan Owens,
To create an auto-rotating two-week schedule for your company, check out this article on creating a dynamic schedule in Excel. It explains how to set up formulas and link dates for automatic updates. You may also find this guide on making a roster in Excel useful, as it provides additional steps to help you design and manage your schedule efficiently. Both articles will support you in building a functional rotating schedule.
Regards
ExcelDemy
This was extremely helpful with just one last remaining issue. In the Conditional Formatting section, I followed the instructions as is and my calendar when selecting different months whited out the dates not present in that month. However, after I added additional columns to capture the schedule of events and pulled in the events from a table, I’m seeing events show up that are from either previous or following months in the dates that have been whited out. How can I get those additional events whited out as well. I checked conditional formatting, and it is pulling in all rows and columns in the calendar.
Hello Kurt Porter,
Thank you for your feedback! I’m glad the tutorial was helpful! Regarding your issue, it seems the conditional formatting rule needs to account for the additional columns and rows introduced for events.
To fix this, try updating the conditional formatting rule to include the logic for checking whether the events correspond to the current month. For example, you can use a formula like:
=MONTH(cell_with_date)<>selected_month
where cell_with_date is the date in your calendar and selected_month is the cell or variable representing the chosen month. Apply this rule to the entire range, including your event columns.
If you need more specific guidance, feel free to share your current formula or setup, and I’d be happy to help further!
Regards
ExcelDemy
Thanks, Shamima. I ended up just doing an ‘IF’ statement in the event cells to check for Date cell Month cell and then “” else….just the full TextJoin formula and it worked. Thanks again, as this was incredibly helpful.
Hello Kurt Porter,
You are most welcome. Glad to hear that the issue is solved. The solution sounds efficient.
Regards
ExcelDemy
Hi, Can you configure the business day number on the date cell and include holidays on the calendar?
Thanks,
Peter
Hello Peter,
Yes, you can configure the business day number on the date cell and include holidays in the calendar. You can use the WORKDAY or NETWORKDAYS functions in Excel to calculate business days while excluding weekends and holidays.
To implement this:
For Business Day Number: Use the formula
=WORKDAY(StartDate, DayOffset, HolidayRange)
1. Replace StartDate with your starting date.
2. DayOffset represents the number of business days to add.
3. HolidayRange is a range containing your holiday dates.
To Highlight Holidays in the Calendar: Use Conditional Formatting with the MATCH function to check if a date falls in the holiday list.
Regards
ExcelDemy
The demo EXCEL file has missing and broken links. It’s not working.
Thanks,
Peter
Hello Peter,
Thanks for your feedback! I just tested the demo Excel file on my side, and it seems to be working fine. Could you please check if the file downloaded correctly and that external links are enabled in Excel?
Schedule in Excel.xlsx
If you’re still facing issues, let me know the specific problem you’re encountering, and I’ll be happy to assist!
Regards
ExcelDemy